Database design guide needed

Question

The dairy farmer, who is also a part-time caricaturist, has several herds of cows. He assigned each cow to a specific herd. In each herd, the farmer has one cow, which is his favorite - often this cow is represented in the cartoon. A few dissatisfied in each herd, mostly those who feel that they were supposed to appear in the cartoon, do not agree with the farmer's choice of a beloved cow, which they scornfully call the sacred cow. As a result, each herd has now elected a herd leader.

Here is what I think the tables should look like you can tell me if this can be done better? So far, I have done a lot for many, using my favorite table, because the intermediate link is the best solution possible, and SQL queries are not needed, it's 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 

@ 3: 01 pmEST edited image is this correct?

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

added a new image @ 8: 57 a.m. 7/20/2010 can someone criticize this ERD, please Erd2 http://img37.imageshack.us/img37/5794/capture3fc.png

added a new image @ 12: 47 pm 7/20/2010, if there is no objection, this is the final version for each comment mark ERD http://img651.imageshack.us/img651/691/capture4b.png

Answer 1

Michael:

What are the nouns in the statement of the problem 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 

Since there is only one farmer, leave it for future discussions. This is your entity base.

What attributes do each object 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) 

Therefore, some of these attributes refer to other entites, and some do not. Attributes that do not reference other underlying objects are simple. Others require more attention.

 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 determine if the relationships between objects are mandatory or optional, and whether the relationships are one-to-many or many-to-many relationships.

Answer 2

Discussion element, so I made it a Community Wiki.

One thing that is not suitable for the relational model is that SacredCow and HerdLeader, which are at the Stud level, actually point to the Cows that are members of that Stud.

Say your Studs are Star and Cross. Details for Starfire may be given by Rigel as SacredCow and Castor as HerdLeader, but the Cow table may show Castor as a member of the Cross. In practice, when creating a new Herd, you come across a chicken and egg scenario when you either have a herd without cows (and therefore no HerdLeader / SacredCow), or a cow without a herd.

An alternative model will have a “Cow” table indicating whether a particular cow is HerdLeader and / or SacredCow for their herd. [In the physical implementation, a unique constraint could be set to ensure that each Stud has only one cow, which was SacredCow, and one cow, which was HerdLeader.]. There would be no SacredCow or HerdLeader in the Herd table. This model cannot ensure that each herd has HerdLeader and SacredCow.

Both are models. Both have flaws. On a logical level, I will probably go with the first, as it is more normalized. At the physical level, I will consider which inconsistency will be more complex and probable, and I would choose the model that best prevented it.

Answer 3

I like that you actually tried to do it yourself. Actually, you're almost there, so you haven't done a bad job at all.

Look at this in terms of Object / Entity.

Objects include the following:

• Cows
• Herds
• CowHerds (you need to associate the cow with what you hear)
• CowCartoon (one of the cows is a favorite, you can always refer to this cow, heard, climbing the chain, using the cow).

---

Cows

• CowID (primary key)
• CowName (varchar, this could potentially be the key, but can the cow have the same name?)

Herds

• HerdID (primary key)
• HerdName (It could also be a primary key, ultimately this is your solution).
• CowID (Foreign Key, remember that each herd has a cow leader, you can even separate it in another table, but it's really not worth it)

shepherds

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

The combination of the specified field serves as the primary key

Cowcartoon

• CartoonID (cartoon primary key)
• CowID ("beloved" cow, the link on which the farmer is going to write a cartoon)
• Date of publication (date of publication)
• Title ....

---

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

Answer 4

Part 1.

If the following is 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 

You can then implement these rules using 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) 

Note that FK relationships include herdID, not just cowID. This ensures that only those cows in the herd can become sacred bones or herdLeader for this herd.

This design makes things a little difficult to implement, but not impossible. Foreign keys on Herd must be set aside in a database such as Oracle, because we need to be able to insert rows for the herd before we can insert rows for the cow, and the Stud needs at least two cows (sacredCow and herdLeader).

Part 2.

The next task is to implement the following restriction:

 only a Sacred Cow may be featured in a Cartoon 

One way to do this is to split the cows into two separate relationships: 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 construct, the constraint (sacredCow! = HerdLeader) is no longer required, because by definition they are now different cows.)

Answer 5

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

Flock Table

 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)