A dairy player, who is another part-time cartoonist, has several herds of cows. He's designated each cow to particular herd. In each herd, the player has one cow that's his favorite - frequently that cow is featured inside a cartoon. A couple of malcontents in each herd, mainly individuals who feel they ought to have made an appearance within the cartoon, disagree using the farmer's selection of a popular cow, whom they disparagingly make reference to because the sacred cow. Consequently, each herd presently has chosen a herd leader.

This is exactly what I believe the tables need to look like are you able to tell me if it is possible better? To date I am carrying out a many to a lot of while using favorite table because the intermediate is the perfect solution also no SQL claims are essential case for design reasons.

Thanks ahead of time

Table Herd           Table Favorite               Table Cartoon   Table Cow
PK herdID          Intermediate Table             PK cartoonID     PK cowID
   herdname                                          cartoonTitle     cowName
   herdleader                                        cartoonType

edited image @ 3:01pmEST is correct?


added new image @ 8:57am 7/20/2010 can someone critique this ERD please Erd2

added new image @ 12:47pm 7/20/2010 unless of course there's any objections this is actually the final draft per Mark's explanation mark ERD


Do you know the nouns within the problem statement, and just how most of them exist?

Farmer  - There is one farmer
Cow     - There are many cows
Herd    - There are many herds
Cartoon - There are many cartoons

As there's just one player, leave him from future discussions. They are your base organizations.

What characteristics does each entity have?

Cow     - each cow has a name
        - each cow is a member of a herd
Herd    - each herd has a name
        - each herd has a cow that is the sacred cow
        - each herd has a cow that is the herd leader
Cartoon - each cartoon has a name
        - each cartoon may have a cow that appears in it 
             (not specified definitively)

So a few of these characteristics reference other entites, plus some don't. The characteristics that don't reference other base organizations are pretty straight forward. Another ones require more consideration.

Can a cow be a member of more than one herd?
Must a cow be a member of a herd?
Can a herd have more than one cow that is the sacred cow?
Must each herd have a cow that is the sacred cow?
Can a herd have more than one cow that is the herd leader?
Must each herd have a cow that is the herd leader?

These questions help outline set up associations between your organizations are mandatory or optional, and if the associations is one-to-many or many-to-many.

Attorney at law item, so I have managed to get a residential area Wiki.

One factor the relational model does not prosper is enforcing the SacredCow and HerdLeader held in the Herd level really indicate Cows which are people of this Herd.

Say your Herds are Star and Mix. The particulars for that 'Star' Herd can provide Rigel because the SacredCow and Castor because the HerdLeader, however the 'Cow' table may show Castor as part of the 'Cross' Herd. Used, when designing a brand new Herd, you face a chicken and also the egg scenario whenever you either possess a Herd without any Cows (and therefore no HerdLeader/SacredCow) or perhaps a Cow with no Herd.

An alternate model might have the 'Cow' table showing whether a specific Cow may be the HerdLeader and/or SacredCow for his or her herd. [Inside a physical implementation, it might be feasible for a distinctive constraint to enforce that each Herd only had one cow which was a SacredCow and something cow which was a HerdLeader.] .The "Herd" table wouldn't possess the SacredCow or HerdLeader. This model would neglect to enforce that each herd were built with a HerdLeader along with a SacredCow.

Both of them are models. Have defects. In the logical level, I'd most likely opt for the previous because it is more Normalised. In the physical, I'd be be thinking about which inconsistency could be more difficult and more prone to occur, and I'd be picking the model that best avoided it.