Database design guidance needed

Question

A dairy farmer, who is also a part-time cartoonist, has several herds of cows. He has assigned each cow to a particular herd. In each herd, the farmer has one cow that is his favorite - often that cow is featured in a cartoon. A few malcontents in each herd, mainly those who feel they should have appeared in the cartoon, disagree with the farmer's choice of a favorite cow, whom they disparagingly refer to as the sacred cow. As a result, each herd now has elected a herd leader.

This is what I think the tables should look like can you let me know if it can be done better? So far I'm doing a many to many using the favorite table as the intermediate is this the best possible solution also no SQL statements are needed this is just for design purposes.

Thank you in advance

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

edited image @ 3:01pmEST is this correct?

cowErd http://img838.imageshack.us/img838/1268/capture3h.png

added new image @ 8:57am 7/20/2010 can some one critique this ERD please Erd2 http://img37.imageshack.us/img37/5794/capture3fc.png

added new image @ 12:47pm 7/20/2010 unless there's any objections this is the final draft per Mark's explanation mark ERD http://img651.imageshack.us/img651/691/capture4b.png

Answer 1

Michael:

What are the nouns in the problem statement, and how many of them are there?

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

As there is only one farmer, leave him out of future discussions. These are your base entities.

What attributes 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 some of these attributes reference other entites, and some do not. The attributes that do not reference other base entities are simple. The other 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 whether or not the relationships between the entities are mandatory or optional, and whether the relationships are one-to-many or many-to-many.

Answer 2

A discussion item, so I've made it a Community Wiki.

One thing that the relational model doesn't do well is enforcing that the SacredCow and HerdLeader held at the Herd level actually point to Cows that are members of that Herd.

Say your Herds are Star and Cross. The details for the 'Star' Herd may give Rigel as the SacredCow and Castor as the HerdLeader, but the 'Cow' table may show Castor as a member of the 'Cross' Herd. In practice, when creating a new Herd, you face a chicken and the egg scenario when you either have a Herd with no Cows (and hence no HerdLeader/SacredCow) or a Cow without a Herd.

An alternative model would have the 'Cow' table indicating whether a particular Cow is the HerdLeader and/or SacredCow for their herd. [In a physical implementation, it would be possible for a unique constraint to enforce that every Herd only had one cow that was a SacredCow and one cow that was a HerdLeader.] .The "Herd" table wouldn't have the SacredCow or HerdLeader. This model would fail to enforce that every herd had a HerdLeader and a SacredCow.

Both are models. Both have flaws. At the logical level, I'd probably go with the former as it is more Normalised. At the physical, I'd be be considering which inconsistency would be more troublesome and more likely to occur, and I'd be picking the model that best prevented it.