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