Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2006
    Posts
    17

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

    Hi Guys,

    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.fr

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When 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

  3. #3
    Join Date
    Feb 2006
    Posts
    17

    Not an assignment

    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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
    Code:
    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...)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2006
    Posts
    17

    Thanks

    Hi Blindman,

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





    Quote Originally Posted by blindman
    Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
    Code:
    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...)

  7. #7
    Join Date
    Feb 2006
    Posts
    17
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Code:
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?

    -- This is all just a Figment of my Imagination --

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tomh53
    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

  11. #11
    Join Date
    Feb 2006
    Posts
    17
    Hi again,

    there are various tasks/activities that for a badge requirement....
    Thanks again

  12. #12
    Join Date
    Feb 2006
    Posts
    17
    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

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If there is only one medical file for each scout, then consider simplifying your schema by including the medical information in the scouts table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2006
    Posts
    17

    Talking thanks

    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

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •