Please Reveiw My Design

[jlstark1]

Access experience: Newbie
I am the training director for a bus company. We have 120 drivers. Part of our training is an annual Roadeo where the drivers participate in 15 skill events. Right Turn, Left Turn, Serpentine etc.
The drivers are divided into 2 groups one in June and one in September. Then each of those groups are divided into 3 groups--Green, Red and Blue and given a participant # as a way to track them. It is possible for a driver in both June and September to be on the Red Group and be participant #1 but of course be a different driver. Each of the 3 groups have GroupLeader who is one of the drivers.
I want to be able to track their scores for each skill event and do it from year to year so we can compare their scores from year to year and track their improvement.
I would appreciate your input. Here is the design I have made:
tblDrivers
DriverID (PK)
FirstName
LastName

tblGroupColors
ColorID (PK)
GroupColor

tblGroupLeader
GroupLeaderID (PK)
DriverID (FK)

tblGroups
GroupID (PK)
ColorID (FK)
DriverID (FK)
RoadeoID (FK)
ParticipantNumber

tblRoadeo
RoadeoID (PK)
RoadeoDate

tblRoadeoEvents
RoadeoEventID (PK)
RoadeoEvent
PossiblePts

tblResults
ResultsID (PK)
DriverID (FK)
RoadeoEventID (FK)
EarnedPts

 

[05me39]

Hi,

I dont think you need a separate table for the group leader. there can be a field in the drivers table that identifies a particular driver as leader. something like a remarks field.

Regards

 

[jlstark1]

Actually the GroupLeader is just there for the convenience of a report needed on the day of the Roadeo. I could pull that from the drivers table. Thanks

 

[spikepl]

Kudos for making the design effort! Pretty good!


Think about this:

tblGroups
----------
GroupID (PK)
GroupColourID
RoadeoID (FK)


tblGroupMembers
-------------------
GroupMemberID (PK)
GroupID( FK)
DriverID (FK)
ParticipantNumber
GroupLeader (Yes/No)

tblEvents
-----------------
RoadeoEventID (PK)
RoadeoID (FK)
RoadeoEvent (text)
PossiblePoints

 

[MarkK]

I'm immediately suspicious of any table that carries only one value: It can make sense for some look-ups, so your Colour table makes sense since it encapsulates a discrete list of typed objects which may be subject to change over time, but your tblGroupLeader contains one foreign key, and tblRoadeo, contains one date. These values are in fact attributes of something else and never merit a whole table to themselves.

This would be like having a whole table for FirstNames. Yeah, you could make it work, but a first name is inextricably a dimension of a person. So just put that field in the Person table.

In respect to your data: GroupLeader is a distinction that belongs to something. It's a dimension or a measure of something. Store that data in the table of the thing that it describes.

Similarly with dates: A date measures the time dimension of an event the same way that inches measure the length dimension of an object. Store that dimension in the same record as the object/event it describes. In this case I would say that your date should be stored in tblResult. That is the object that does in fact, in the real world, occur in time.

 

[jlstark1]

To spikepl

Tell me if I am following your logic correctly:

A table should be broken down to smallest bit of information to keep track of and in my tblGroups I was trying to track information about our 3 groups and the members of each group. By adding the tblGroupMembers we separate that information into 2 separate tables. Correct?

You added the RoadeoID to the tblEvents. Is that to aid in tracking their scores from year to year?

Thanks for your help. I appreciate it.

 

[spikepl]

Not quite. The question to ask is "WHat is composed of what" and "What shares/has what property". So a group is composed of members, and yet the group itself shares certain properties: here colour and participation in a given roadeo. Hence tblGroups (for the common bits) and tblGroupMembers for each individual.

Events are not stand-alone enitities in the universe, but in fact comprise the roadeos.
Without the relation between an event and a roadeo, you have no means of telling who got what points when.

 

[ButtonMoon]

Hi jlstark1,

It is quite possibly going too far to have tables for Roadeo and Colours. Those appear to be things that could just be single attributes in other tables (date of the event, colour of the group). If you had other attributes of Roadeos or Colours then that would be one good reason to create tables for them.

A sensible guide for starting any database design is to aim to satisfy Boyce-Codd Normal Form. If you are interested in learning some of the principles behind good database design then I recommend you read up on at least that. What you have done is probably perfectly reasonable for the task though. Good luck!