problem with my tables (1 Viewer)

[sam.frost]

Hi,

I would really appreciate some help on a database i'm working on.
I have been asked to create a database at work for a client. Basically it is for a sports club which offers sessions to different clubs.

each club can have upto 6 sessions each session can have up to 6 groups.

so far i have come up with:

Table: Club info
Fields: Name, Club ID, contact, address, tel, mob,email

Table: Session info
Fields: Club ID, Session ID, number of groups, Equipment

Table: Group Details
Fields: Session ID, Group Number, Age, Size, Sex

I created relationships between Club ID and Session ID.

Problem is i have realised that this wont work because the session id will not be unique seeing as each club could have 1-6 sessions, also the same with group table. a club called test could have 3 sessions each having 1 group.

Could anyone shed some light on this?

Please help.

 

[grnzbra]

What is a session? Is it one of six attendances by a club? What is Equipment? If equipment records the equipment used by the club, you would have a one to many relationship between Club and Session tables. However, if Equipment referrs to equipment needed by any club for that session, you may have a many to many relationship, in which case, you need another table.

Let's say I manage a shooting facility that is used by various police departments for training. If I have a program in which the department firearms instructor can bring up to six groups of people to my facility six times and when they get there, they use what they want, I would have three tables similar to yours.

However, if I am training police officers in a number of firearms, I would have six records in the sessions table, let's say pistol, shotgun, tactical rifle(assault rifle), sniper rifle, submachinegun, house-of-horrors. Each record would record stuff about equipment needed for a training in that particular type of firearm as well as other stuff about that session such as range to be used, max number of students and minimum number of instructors. It would not have any ID for the department.

I would need another table to join these two, which would have information about each department's instance of each session. This might include number of participants, number of instructors, number of groups, any problems that occurred, dates and times, etc. This table would include both DepartmentID (your ClubID) and SessionID, perhaps in a compound key. This table would have 6 records for each department.

A similar situation would exist for Groups. If the firearms instructor brings in a bunch of students, you might group them by age, sex, skill level, department rank, etc.

If you were running a program, you would have a table specifying how the groups are broken down and there would be only six records. You would then have a SessionsGroups table that would specify things like equipment for each group for each session. For example a group of novices in the handgun session would perhaps be given revolvers because they are simpler to operate, while a more advanced group might be given semi-automatics and a group of high ranking offices would be given rubber guns and pretty holsters. This table would have a max of 36 records.

You might also want a participant's table with links to DeptID, SessionID and GroupID as well as other personal information.

The tables you have seem to indicate a situation where sessions are just time and groups are just bunches of people and the club does with them what they will. In that case, you don't need any more tables and can get by with using the ClubID and SessionID fields of the Sessions table as a compund key and adding ClubID and SessionID to the Group table and combine them with the GroupID in a compound key.

OR

You could add an autonumber field to each table and use them as the unique key field.