Designing Education based database

[campgirl1989]

I'm creating a database for my summer camp. Each badge has a different set of requirements. i.e. Climbing requirements = 1a, 1b, 2a, 2b, 2c, 3, 4, 5 and Art requirments = 1, 2, 3, 4

I have to keep track of which requirements the students have finished.

Is there an easier/better way to set it up besides creating a seperate table for each badge that lists the requirements in seperate fields?

Also, during a session more than one badge might be taught and one badge might be taught over two sessions. What are the best ways to address these issues?

 

[HiTechCoach]

Is there an easier/better way to set it up besides creating a seperate table for each badge that lists the requirements in seperate fields?

I would use a single table for the badges and a child table ( one-to-many) that would have one record for each requirement. This will allow you to haev as many requirnents ads needed.

 

[Mr. B]

Campgirl1996,

I really think you may want to consider having even more tables that what has been mentioned.

I would think that to track each element for each badge completed for each camper, you would need a Campers table, a Badge table and. a requirements table where each requirement for each badge can be defined. You would then need a table where you can link the campers to the each of the requirements. You may also want to define a table that will have the classes that will be taught. If so, then you might also need a table that would define the badges that are available in each class.

Database design is never as simple as it may appear on the surfact. Especially if you are going to have your data structrue normalized.

HTH

 

[campgirl1989]

Mr. B, you and I are on the same page. I'll have a table for scouts, one with a list of all the badges, troops, times, instructors, directors, program areas the badges fall into, campsite the troops are staying in, fees for some badges, and several tables linking these all together. I was able to figure out everything up to this point but I keep getting hung up on the above mentioned problems.

 

[Mr. B]

Campgirl1996,

I still think that I would have Badges in one table and the requirements definitions for each badge in another table. Then you can have table for linking Scouts with with badges and requirements completed. This table would have a PK and a bunch of FK to the Scouts table, the Badges table and the Requirements table. There would also possiblly be a few other fields such as a Completed date for each record, etc. This way you would have one record in this table for each requirement completed and be able to slice and dice the data in any way you need it for reporting purposes.

As for the Sessions and the Badge taught, again an linking table may be your answer. One thing to remember is that when in realy life you have a many to many relationship, you need a special linking table so you can create that kind of relationship.

Just my thoughts.

HTH

 

[campgirl1989]

Ok that makes a lot of sense. Thank you for all the help!

 

[just.a.guy]

Just a brief comment.
It sounds like the badge to requirements is a "many to many" relationship.

Do you want each badge to have many requirements?
And do you want one requirement to applicable to more
than one badge?

If this is the case, then you need to use a relationship
table. This table useually has just 3 columns.
a) primary key (an autonumber)
b) primary key from the badge table
c) primary key from the requirements table.

Similarly you may need a "many-to-many"
relationship between "class" and "requirements".

Hope this helps.