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

Yes it is a homework question. I'm just trying to do it correctly, will this be a problem? — Michael Quiles, Jul 19 '10 at 16:49
Almost certainly homework, but you have had an attempt and that should be respected. You currently don't store individual cows. Think about them. — Michael J, Jul 19 '10 at 16:50
Thank you, I'm not lazy I want to learn I just need a little help now and then until I can get good at it. — Michael Quiles, Jul 19 '10 at 16:58
With that said how would you improve it what have I done wrong that can be done better and I added a cow table per Michael J hint. — Michael Quiles, Jul 19 '10 at 16:59
@Michael, now think of the relationships between Cows and Herds, and between Cows and Cartoons. Are there any many-to-many relationships there? — , Jul 19 '10 at 17:01
@michael, also, you haven't indicated the structure of the Favorite table. Are you sure that this is best implemented as a table in its own right? — , Jul 19 '10 at 17:03
A cow "the farmer has one cow that is his favorite - often that cow is featured in a cartoon" — Michael Quiles, Jul 19 '10 at 18:41
Well, if the relationship is "cartoon features cow / cow is featured in cartoon", why is the relationship between Cartoon and Herd? — Adam Musch, Jul 19 '10 at 18:51
Well I thought since "In each herd, the farmer has one cow that is his favorite" means that there are many herds and from that herd on cow is selected to be in the cartoon I'm guessing this isn't correct, does the rest look ok? — Michael Quiles, Jul 19 '10 at 18:54
@Michael, just one further observation - at the moment, you can only relate sacred cows and herd leaders to their particular herds; if a cow is neither sacred nor a leader, there is no way of telling which herd it belongs to. — , Jul 20 '10 at 10:29
Nice question but I'm 'steer' -ing well clear of this: the implied temporal element of the `cartoon` table increases the complexity e.g. a herd's current sacred cow could change (e.g. due to death) requiring database level temporal constraints, sequenced primary keys, etc. — onedaywhen, Jul 20 '10 at 14:24
@Michael, your new design actually enables a many-to-many relationship between cows and herds, if you use the featuredCow table for all cows (instead of just cows featured in a cartoon). On the other hand, if only cows featured in a cartoon appear in the featuredCow table, you still can't link "other" (ie. non-leader, non-sacred) cows to their herds. — , Jul 20 '10 at 14:46
@Michael, the cartoon to favoriteCow (aka appearance) table is a one-to-many link - favoriteCow/appearance is a link entity between Cow and Cartoon, ie. there's a many-to-many relationship between them. If you only want no more than one cow to appear in any given cartoon, then you don't need the link table - just a foreign key on cow ID in cartoon. — , Jul 20 '10 at 17:37

score 5 · Accepted Answer · edited Jul 20 '10 at 05:10

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.

score 4 · Answer 2 · answered Jul 19 '10 at 23:17

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.

the relational model, if implemented fully, can enforce these constraints easily. Unfortunately, the RM isn't fully implemented in most RDBMS's out there :( — Jeffrey Kemp, Jul 20 '10 at 04:51
@Chris Kaminski: for example, a TRDBMS could feature database constraints (for which the overall database must satisfy some specific condition, where the condition in question can refer multiple tables) and multiple assignment (data may be updated in many tables and be considered an atomic action for the purposes of constraint checking) e.g. a D language as specified in The Herd Manifesto... sorry, I mean, The Third Manifesto (http://en.wikipedia.org/wiki/The_Third_Manifesto). — onedaywhen, Jul 20 '10 at 14:12

score 1 · Answer 3 · edited Jul 20 '10 at 03:09

1

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.

edited Jul 20 '10 at 03:09

APC

144,005
19
170
281

answered Jul 19 '10 at 17:11

JonH

32,732
12
87
145

CowHerds table is, in my opinion, in correct -- a Cow can belong to one and only one herd, so the Cow table should have a HerdID column that is a foreign key back to Herd. The propsed design permits each cow to be a member of each herd. Further, the Herd table needs two references back to the Cow table - the sacred cow and the herd leader, which may or may not be the same cow. – Adam Musch Jul 19 '10 at 17:47
when you say references do you mean a recursive relationship? – Michael Quiles Jul 19 '10 at 18:00
When I say reference, I mean foreign key constraint. – Adam Musch Jul 19 '10 at 18:06
@Adam: While I agree that the CowHerds table is unnecessary, I would suggest that only the leader cow belongs in the herd table. While it's not explicitly stated, it seems that there is one sacred cow per cartoon, not per herd. Different cartoons could have different sacred cows, which could both belong to the same herd. – Allan Jul 19 '10 at 18:06
@Allan: I disagree with your interpretation of the requirements, as the requirements do explicity state "In **each herd**, the farmer **has one cow** that is his favorite - often that cow is featured in a cartoon." Similarly, "**each herd** now **has** elected **a herd leader**." As such, SacredCowID and LeaderCowID belong in the Herd table as foreign key columns back to Cow. Heck, I don't see any requirement to track which cow appears in which cartoon at all, but that I suspect is much more open to debate. – Adam Musch Jul 19 '10 at 18:14
Edited for emphasis on quotes. – Adam Musch Jul 19 '10 at 18:15
@Adam Musch - You could do it that way, and I agree with you however it was not originally mentioned when the OP first asked. But yes you could include the HerdID right directly in the Cows table. It really can work either way. Of course without the cow heard table it is much simpler solution. – JonH Jul 19 '10 at 18:17
OK now I'm confused, which would be the simplest way of implementing this I will upload my diagram in a second so that you guys can tell me what I did wrong and why. thanks for your help guys really appreciate it. – Michael Quiles Jul 19 '10 at 18:20
@Michael Quiles - If one cow can only belong to one herd, then you do not need the CowHerds table. Simply have a herds table with a herdID. And in your cows table add a HerdID field to the cow table. That way a cow only belongs to one herd and you can define it when you first create the cow. – JonH Jul 19 '10 at 19:41
#High on a hill lived a lonely CowHerd...# -- makes me wonder how a farmer differentiates their herds in reality. I suspect it is by some physical attribute (by breed, by the field the cow currently occupies, etc) rather than by assigning a `HerdID` identifier. I have almost zero domain knowledge here but I find `CowHerds` to be jarring. What about `HerdManagement`? – onedaywhen Jul 20 '10 at 14:31
@onedaywhen - It's just terminology. Whatever is easier for you. Again you may not need that table you could reference a HerdID within the cow's table. – JonH Jul 21 '10 at 15:38
@JonH: "It's just terminology" -- I disagree: it's actually metadata. "CowHerd" makes me think of "Goatherd" which is a person who herds goats, so straight off I've got the wrong idea. I find it interesting that the lack of a familiar 'real world' term for the allocation of cows to herds makes me think this is a contrived concept and rather it would be determined by attributes e.g. current physical location, breed, owner, etc. – onedaywhen Jul 22 '10 at 07:59
@onedaywhen - i disagree with you, as it is really up to the op to give the relationship name, it really isn't that big of a deal. – JonH Jul 22 '10 at 12:18

Jeffrey Kemp · Answer 4 · 2010-07-21T02:42:37.767

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

Sacred cows and non-sacred cows are both cows - ie. they share the same attributes and the same key, so they should be the same entity / table. — , Jul 20 '10 at 14:51
@Mark, that is not enough. It is perfectly acceptable to have same set of attributes in two entities if the propositions/semantics applied to entities differ. — Unreason, Jul 20 '10 at 15:15
@Unreason, you can in SQL, but not in a properly normalised schema. — , Jul 20 '10 at 16:03
@Mark Bannister, in this design SacredCow and NonSacredCow *do* have different attributes and different keys (they just happen to have the same names, which may be confusing but is not disallowed). — Jeffrey Kemp, Jul 21 '10 at 02:40
To make it even more explicit: what I'm saying in this model is that, regardless of whether a farmer might say that there is no difference between a SacredCow and a NonSacredCow, in the *context of this model*, the cartoonist (and the cows themselves!) do consider them to be fundamentally different. — Jeffrey Kemp, Jul 21 '10 at 02:47
@Mark Bannister: you are incorrect in saying that a properly normalized database cannot have two tables with the same attributes and keys. I think you meant to say that it violates the principle of orthogonal design. — onedaywhen, Jul 21 '10 at 07:43
@onedaywhen: nope - "the key, the whole key and nothing but the key". — , Jul 21 '10 at 09:47
@Mark Bannister: simple example: `Employees` table with `emp_ID` key plus a bunch of attributes, an `Unsalaried` table with just the `emp_ID` key and nothing but the `emp_ID` key, and a further table `SalariesUnknown` table again with with just the `emp_ID` key and nothing but the `emp_ID key`. Both `Unsalaried` and `SalariesUnknown` are in 5NF yet they share the same keys (`emp_ID`) and attributes (none). — onedaywhen, Jul 21 '10 at 12:39

score 1 · Answer 5 · answered Jul 20 '10 at 15:00

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)

Database design guidance needed

5 Answers5

Cows

Herds

CowHerds

CowCartoon