Database design guidance needed

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
                                                     car开发者_运维技巧toonDate

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.

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.

I like that you've actually tried to do this on your own. In fact you are nearly there, so you haven't done a bad job at all.

Let's look at this from an Object / Entity perspective.

The entities include the following:

• Cows
• Herds
• CowHerds (you have to associate a cow to a heard)
• CowCartoon (one of the cows is a favorite, you can always reference this cow's heard by going up the chain using the cowid)

Cows

• CowID (Primary Key)
• CowName (varchar, this could potentially be the key but cow's may have the same name right?)

Herds

• HerdID (Primary Key)
• HerdName (This could also be the primary key, ultimately this is your decision)
• CowID (Foreign Key, remember each herd has a cow leader, you can even separate this in another table, but it really is not worth it)

CowHerds

• CowID (fK to cows)
• HerdID (fk to heards)

The combination of the above field serves as a primary key

CowCartoon

• CartoonID (primary key of a cartoon)
• CowID (the 'favorite' cow, references which cow the farmer is going to write a cartoon about)
• Published Date (date published)
• Title ....

As mentioned in the comments, you can also get rid of the CowHerds table and reference the HerdID directly in the Cows table.

Assuming a Cow can only belong to one herd at a time and assuming that more than one cow (from different herds, obviously) can appear in the same cartoon, my suggestion:

Table Herd

Herd ID (PK)
Herd Name

Table Cow

Cow ID (PK)
Herd ID (FK)
Cow Name
Is Leader (Boolean)
Is Sacred (Boolean)

Table Cartoon

Cartoon ID (PK)
Cartoon Title
Cartoon Type
Cartoon Date

Table Appearance

Cow ID (PK)
Cartoon ID (PK)

Part 1.

If the following are true:

each Cow must be in exactly one Herd
a Herd must have a sacredCow and a herdLeader
a sacredCow for a Herd must be a Cow in that Herd
a herdLeader for a Herd must be a different Cow in that Herd

Then you could implement these rules with this partial model:

Cow (cowID, herdID) (all mandatory columns)
- primary key (cowID)
- unique (herdID, cowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader) (all mandatory columns)
- primary key (herdID)
- foreign key (herdID, sacredCow) references Cow (herdID, cowID)
- foreign key (herdID, herdLeader) references Cow (herdID, cowID)
- constraint (sacredCow != herdLeader)

Notice how the FK relationships include the herdID, not just the cowID. This ensures that only those Cows in a Herd may be made the sacredCow or herdLeader for that Herd.

This design makes things a little tricky to implement but not impossible. The foreign keys on Herd would have to be made deferrable in a database like Oracle, since we need to be able to insert the rows for a Herd before we can insert the rows for the Cows, and a Herd requires at least two Cows (the sacredCow and the herdLeader).

Part 2.

The next challenge is to implement the following constraint:

only a Sacred Cow may be featured in a Cartoon

One way to do this may be to split Cows into two separate relations: SacredCows and NonSacredCows.

SacredCow (sacredCowID, herdID) (all mandatory columns)
- primary key (sacredCowID)
- unique (herdID, sacredCowID)
- foreign key (herdID) references Herd (herdID)

NonSacredCow (nonSacredCowID, herdID) (all mandatory columns)
- primary key (nonSacredCowID)
- unique (herdID, nonSacredCowID)
- foreign key (herdID) references Herd (herdID)

Herd (herdID, sacredCow, herdLeader)
- primary key (herdID)
- foreign key (herdID, sacredCow) references SacredCow (herdID, sacredCowID)
- foreign key (herdID, herdLeader) references NonSacredCow (herdID, nonSacredCowID)

Cartoon (cartoonID, featuredCow) (all mandatory columns)
- primary key (cartoonID)
- forign key (featuredID) references SacredCow (sacredCowID)

(In this design, the constraint (sacredCow != herdLeader) is no longer required because they are now different cows by definition.)