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.

Scoped calulated measures for unrelated dimensions

Hi,

I am not sure if this is a correct way to design this,

Here is my scenario:

We are migrating an existing databwarehouse application from vb, which used to pull out records using the criteria provided in the input screen. The input conditions are many and multiple selections and combinations constitute the final data that is returned in excel format.

Most of the input conditions specified are filters or slicing of data, which we have modelled as dimensions, and they were existing tables in the database. other filters for dicing the measures which is modelled as the fact were also existing tables.

There are some input conditions that are not dimensions at all, meaning there are no tables which exists for them.

One for example, a collection of 4 values (A,B,C,D) , when A is selected the output excel comes in a paticular format, with calculations specific to a paticular region. and so on for B,C,D.etc

1. Now if I model this as a dimension in ssas 2005, I dont have related fact data for this. what should I do?

2. Anyway , I have created a dimension in ssas 2005 that contains this data. I now want to create calculations (either named or calc measures) that dynamically have formula x+y if the browsed value/queried value of the unrelated dimension is A and a different formula if the viewed dimensiion value is B,etc

meaning I want to have a dynamic calculated measure that changes its formula based on the unrelated dimension that is used in the query's row or column

Is this possible? is this the correct way to model this problem?

Regards

Yes, it is possible. The very related example how to do it - is the Time Intelligence Wizard. When you run it, it modifies DSV (you don't even need to create artificial table for A,B,C and D !) to add such factless attribute members, and then in MDX Script it assigns to them formulas. I.e., you MDX Script would look something like

ArtificialAttribute.A = formulaforA;

ArtificialAttribute.B = formulaforY;

...

etc.

|||

On this note, can I ask you a question.
Isn't using time intelligence an expensive operation during querying?

Rgds

Hari

|||

Sorry, but this question isn't posed in a way that it is possible to answer. It's like asking "Isn't running SQL queries expensive ?". And the answer to this question is also unrelated to the original problem.