scouts, badges, and groups of badges (was "Please help")

Hi Guys,:rolleyes:

I am designing a database for scouts and i am stuck on the ERD(relationships design)...The situation is this: I have an entity 'scout' and entity 'badge'.
A scout can have many badges as obvious. However there are different classification(groups) of badges(main badges, activity badges,Staged Activity badges...). Each of these group of badges consists of different types of badges and finally a badge type has required tasks to accomplish to qualify for it!!

Is there anyone with a suggestion how to model these entities please??

migarich@.yahoo.frWhen you post your homework, please either provide the URL where the assignment was given, or at least scan in the handout you got in class that describes the assignment. It makes life so much simpler if we can do your homework for you in one pass, instead of completing what you've posted so far, only to have you realize that there is another part you need for full credit!

-PatP|||This is not actually an assignment but a system i am designing as a voluntary action for my local scout troup...and there is no specifications as such...the descriptions i have made are from my head and not some handouts as you may think...If there is any part of the description you do not understand i would be grateful to hear...as i said it is only part of the problem that i am having issues with.

Regards|||Ok, I see three detail level tables: Scouts, Requirements, Badges.

I see three linking (many to many) tables, Scout to requirement (showing that the scout has met a requirement), Badge to requirement (showing that the requirement is a pre-requisite for the badge), and Scout to badge (showing that the scout has earned the badge).

I see one "decorative" table, showing that a badge is of a given badge type.

-PatP|||Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Hi Blindman,

This definetely makes sense...I was thinking more of the supertypes and subtypes...Would you say it is feasible?...Thanks

Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Thank you very much...It is now clearer...How about replacing the requirement table by 'Scout_badge' which has 2 key fields as from scout and badge??

Thanks for you help|||Table: Requirements
(Requirement, [pkey]
Badge, [fkey to badges]
Description,
etc...)

Table: ScoutRequirementsCompleted
(ScoutName, [fkey to scouts]
Requirement, [fkey to Requirements]
DateCompleted,
etc...)

When a scout has completed all the requirements for a badge, grant him the badge and add a record to ScoutBadges|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?A UNIQUE CONSTRAINT will take care of a scout only earning a badge once. Making the possesion of a badge a requirement for another badge takes care of the prerequisite.

-PatP|||Hi again,

there are various tasks/activities that for a badge requirement...
Thanks again|||Hi guys, I have a table called camp, Scout and Medical_File...i need to include the details of a scout medical file when the scout is booking a camp so we know what we need to take care of in different activities...does anyone know how to model this. The medical File is not currently linked to the camp at the moment!!
Thanks|||If there is only one medical file for each scout, then consider simplifying your schema by including the medical information in the scouts table.|||That is actually a good idea...i did that before and decided to move it to a separate entity as the enity scout seemed of a big table...

Yeah i have another prob...sometimes scouts go to a camp by patrol but also individually depending on who organised the camp...at the moment i had scout entity linked to camp via payment(camp payment)...do u think i should link patrol as well?

thanks|||Is a scout in only one Patrol?

You should probably store "snapshot" information about the scout and the patrol the belong to in your camp/scout attendance table, as some of the data may change over time (they may join a different patrol), but you would still want to know that they were in the original patrol when they attended the camp.