Database

29-Mar-10 4:00

I have a table of football matches, and each match can have 5 different statuses:
Not started, 1st halftime, Break, 2nd halftime, Ended

My question is, what is the correct way of modeling this?:

Matches (MatchId, Status, ...)
where Status is an integer or a string corresponding to each status

vs

Matches (MatchId, StatusId, ...)
where StatusId is a foreign key to MatchStatuses,
MatchStatuses (MatchStatusId, Description)

vs

Matches (MatchId, StatusId, ...)
where StatusId is a foreign key to MatchStatuses,
MatchStatuses (MatchStatusId, Description, IsNotStarted, IsFirstHT, IsBreak, IsSecondHT, IsEnded)

for example, a registry in this case would be: (1, "First Half time", false, true, false, false, false)

THANKS!

J4amieC29-Mar-10 4:56

J4amieC

29-Mar-10 4:56

I would go with 1 (ok solution) or 2 (better solution)

Solution 3 was just dumb to the max.

Alivemau529-Mar-10 5:01

29-Mar-10 5:01

With (1) you would have to hardcode any combobox (for example) containing the different possible entries.

With (2) you wouldn't know which one is each..

Diff between 2 and 3 would be that in the program itself you would have to use:

someMatch.Status.Finished == true
vs
someMatch.Status.Description == 'finished'

But im not convinced, though, of solution (3)

J4amieC29-Mar-10 5:17

J4amieC

29-Mar-10 5:17

Alivemau5 wrote:
With (2) you wouldn't know which one is each..

public enum MatchStatus
{
  NotStarted = 1,
  FirstHalf = 2,
  HalfTimeBreak = 3
  //.. etc
}

If you have code relying on this, the enumerate it in code.

Alivemau529-Mar-10 6:24

29-Mar-10 6:24

You have convinced me.

And for users privileges do you think its a good idea to have the following?:

Users (UserId, UserTypeId, ..)
UserTypes (UserTypeId, Name, MayAddMatch, MayDelMatch, May..., May.., May... )

with registries like (1, "Admin", true, true, true...)

(i'm not doing an offtopic.. its a similar question)

programmervb.netc++29-Mar-10 9:11

programmervb.netc++

29-Mar-10 9:11

Alivemau5 wrote:
With (2) you wouldn't know which one is each..

You could know which one is which this is how we do it.
This may not be the cleanest way but it is very easy to maintain.
We create an integer array such as Dim blah() as Integer and set a default size for it close to what we think the number of records will be to minimize the use of redim.
Then do something like this with the record set you retrieve.

While myReader.Read
            cmbControl.Items.Add(myReader.GetString(1))
            lKeyIndex(lIndex) = myReader.GetValue(0)
            '
            lIndex += 1
            ' see if we need to redimension the array...
            If lIndex Mod iCurrentDim = 0 Then
                iCurrentDim = iCurrentDim + iDefaultDim
                ReDim Preserve lKeyIndex(iCurrentDim)
            End If
End While

Here is a example query.

SELECT `UniqueNumber`, `Description` FROM `ct_contsources_master` WHERE `Inactive` = 0 ORDER BY `Description`

Notice the Inactive flag...
If you use this than if a row is no longer valid you can set it to 1...therefore not populate the combo.

EDIT..
Oh yeah this will help..

Public Function fiSetComboIndex(ByVal lKey As Long, ByRef cboControl As ComboBox, ByRef lKeyIndex() As Long) As Integer
       ' this routine sets the current "SelectedIndex" to the lKey passed in...
       Dim iII As Integer
       Dim iKeyIndexLength As Integer = lKeyIndex.Length - 1
       '
       fiSetComboIndex = 0
       For iII = 0 To iKeyIndexLength
           If lKeyIndex(iII) = 0 And iII > 0 Then
               Exit For
           End If
           If lKeyIndex(iII) = lKey Then
               fiSetComboIndex = iII
               Exit For
           End If
       Next
   End Function

Use this to get the value that is selected.

blah(comboBoxName.SelectedIndex)

Store the result of that in the main table.

Humble Programmer

riced29-Mar-10 5:57

riced

29-Mar-10 5:57

Depends on what questions you want to answer.
If the only question you want to answer is 'What is the status of a match?' then first suggestion is good enough. This effectively means you have a one-one relation between matches and statuses.

If you want to answer questions like 'What were half-time and full-time scores of a match?' then you have a one-many relation between matches and statuses so you need to use your second suggestion.

Your third suggestion seems to offend first order normalization.

I'd go for the second approach because it's more flexible. This may also mean moving some columns from Matches into the statuses table (e.g. the score since you have both half and full time scores).

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Alivemau529-Mar-10 6:30

29-Mar-10 6:30

Well my second suggestion was more like a table with 5 registries than a table with n registries, that is:

(1, Not started)
(2, First Half)
(3, Break)
(4, Second half)
(5, Ended)

And no more columns.. not this:
(1, Not started)
...
(23, Not started)
I think you understood that

This second suggestion was thinking on that I could fill any later combo (for example) without hardcoding it.. but the enum solution works and I wouldnt need to hardcode.

Eddy Vluggen29-Mar-10 7:01

Eddy Vluggen

29-Mar-10 7:01

Alivemau5 wrote:
My question is, what is the correct way of modeling this?

Normalization[^] would be the way to go, with most tables ending up in the third normal form.

A joking reference during design would be "The Key, the whole Key, and nothing but the Key, so help me Codd[^]" Smile | :)

I are Troll Suspicious | :suss:

programmervb.netc++29-Mar-10 8:53

programmervb.netc++

29-Mar-10 8:53

I say 2 because that would be normalized. The enum would prevent hard coding but you really don't want to have to refer to app anytime you want to know what the value is...
For instance if you are just looking at the data in the table not in the app.
Also if you do it the second way rather then the enum you don't have to modify the enum every time you add a row.

Humble Programmer

PIEBALDconsult29-Mar-10 18:12

PIEBALDconsult

29-Mar-10 18:12

The second way, definitely -- plus an enumeration in your code.

P.S. See my GenOmatic[^].

Alivemau530-Mar-10 10:44

30-Mar-10 10:44

Isn't bad that the table with states would be finite (5 registries) and readonly?

how to check this date is 6 months or 1 year for given condition

PIEBALDconsult30-Mar-10 12:09

PIEBALDconsult

30-Mar-10 12:09

No, and it would leave room for more in the future. And maybe you would use it as the base for other sports, like American football or baseball or who knows what.

It's a proper pattern to use anyway, even if there are only two entries.

developerit29-Mar-10 3:55

developerit

29-Mar-10 3:55

hi, iam using asp.net with c# with sql server databae

the table fields are

username nvarchar
startdate datetime
enddate datetime
paytype int
reminder int

while registering the user will selects the startdate and enddate and the pay type is 6months or 1year and he will select the reminder before 10days
then if he selects for 6months then before 10days i have to send email. if he selects for 1year i have to send for every 6months before 10 days
then how to write the querry for this condition please give the example which helps me.

Re: how to check this date is 6 months or 1 year for given condition

Eddy Vluggen29-Mar-10 7:08

Eddy Vluggen

29-Mar-10 7:08

developerit wrote:
then how to write the querry for this condition please give the example which helps me.

You'll need to write a SELECT[^] statement, with a filter on the EndDate.

You'd have to compare whether this EndDate equals "today" + 10 days. You can get todays date by using the GETDATE()[^]-function, and add 10 days using the DATEADD[^] function.

Good luck Smile | :)

I are Troll Suspicious | :suss:

SQL Query

dabuskol29-Mar-10 2:10

dabuskol

29-Mar-10 2:10

Most of the time there are two/three record in the table.
How can you do it in SQL

IF Status = 1 or status = 0 THEN Desc = "In Progress"
TabID Status

T1 1
T1 0

If Status = 1 or status = 1 THEN Desc = "Ok"
TabID Status

T1 1
T1 1

If status = 0 or status = 0 THEN Desc = "failed"
TabID Status
T1 0
T1 0

Select should give me 1 row only

My example is not correct, kindly enhance.

select tabid, GroupDesc = case when groupstatus = '1' or groupstatus = '0'  then 'In Progress' 
                   else 'Ok' end
from tab1
where tabid = T1

Dabsukol

Re: SQL Query

Michael J. Eber29-Mar-10 11:26

Michael J. Eber

29-Mar-10 11:26

Try this:

SELECT tableIdentity,
       case when groupstatus = 1 or groupstatus = 0 then 'In Progress' else 'OK' end GroupDesc
from tableOne
where tabid = T1;

You should also physically confirm that you have only a single row named T1.

Is this query bad.....?

Hum Dum29-Mar-10 1:18

Hum Dum

29-Mar-10 1:18

Select Min(Priority) from Outbox where [Transmitted] = false and [FolderId] IN (7,8) and
 [Deleted] = false and [InterfaceNo] =@InterfaceNo and [SwitchingAddressNo] NOT IN 
(Select [SwitchingAddressNo] from AddressBook where [Block] = true and [Deleted] = false

The above query i wrote, and its working fine.
I want to know the performance related issues with this?

If its ugly?
then how can i improve the same.

PSK_29-Mar-10 1:30

PSK_

29-Mar-10 1:30

You are using NOT IN in you query, it is not good for the query performance as the query is not going to use benefits of indexes defined on the table and will use a sequential scan on the entire table, try to use NOT EXISTS.

WWW, WCF, WWF, WPF, WFC .... WTF

programmervb.netc++29-Mar-10 9:24

programmervb.netc++

29-Mar-10 9:24

Does this apply to all DBs? I have never used NOT EXISTS...

Humble Programmer

PSK_29-Mar-10 17:26

PSK_

29-Mar-10 17:26

I am sure about SQL Server, Oracle and Sybase but no idea about other DB servers.

WWW, WCF, WWF, WPF, WFC .... WTF