CODEWITHSUNDEEP
database-designdata-modelingtable-structure
Onorio Catenacci
Onorio Catenacci

Reputation: 15293

One To Many To Itself

How would one structure a table for an entity that can have a one to many relationship to itself? Specifically, I'm working on an app to track animal breeding. Each animal has an ID; it's also got a sire ID and a dame ID. So it's possible to have a one to many from the sire or dame to its offspring. I would be inclined to something like this:

ID INT NOT NULL PRIMARY KEY
SIRE_ID INT 
DAME_ID INT

and record a null value for those animals which were purchased and added to the breeding stock and an ID in the table for the rest.

So:

  1. Can someone point me to an article/web page that discusses modeling this sort of relationship?
  2. Should the ID be an INT or some sort of String? A NULL in the INT would indicate that the animal has no parents in the database but a String with special flag values could be used to indicate the same thing.

  3. Would this possibly be best modeled via two tables? I mean one table for the animals and a separate table solely indicating kinship e. g.:

    Animal

    ID INT NOT NULL PRIMARY KEY

    Kinship

    ID INT NOT NULL PRIMARY KEY FOREIGN KEY

    SIRE_ID INT PRIMARY KEY FOREIGN KEY

    DAME_ID INT PRIMARY KEY FOREIGN KEY

I apologize for the above: my SQL is rusty. I hope it sort of conveys what I'm thinking about.

Upvotes: 5

Views: 8575

Answers (9)

tsilb
tsilb

Reputation: 8037

I don't know about animal breeding, but it sounds like your Sire_ID is the father and Dame_ID is the mother? No problem. One row per animal, null sire_ and dame_ID's for purchased animals, I don't forsee any problems.

[ID],[Sire_ID],[Dame_ID];
0,null,null  (male)
1,null,null  (female)
2,null,null  (female)
3,0,1 (male)
4,0,2 (male)
5,null,null  (female)
6,3,5
7,4,5

and so forth. You would likely populate a TreeView or XmlNodeList in a while loop...

While (myAnimal.HasChildren) {
 Animal[] children = GetChildren(Animal.ID)
 for (int x=0; x<children.length; x++) 
  myAnimal.Children.Add(children[x]);
}

In this case, Animal.Children is a Collection of Animals. Therefore, myAnimal.Children[0].Father would return myAnimal. .Parent[] could be a collection of its two parents, which should work as long as [0] is always one parent (father) and [1] is always the other (mother).

Make ID an Autonumber PK and assign Sire_ID and Dame_ID programatically by returning the IDs of its parents. No foreign key relationships should be neccessary though both parent IDs could reference back to ID if you really want to.

Upvotes: 1

borjab
borjab

Reputation: 11655

Seems like you want to build something like a tree.

What about something like?:

 ID          Primary Key,
 Parent_ID   Foreing_Key
 ( data )

There are some functionality for doing querys in tables with relations to themselves. See the syntax of Connect By: http://www.adp-gmbh.ch/ora/sql/connect_by.html

Upvotes: 0

Thilo
Thilo

Reputation: 262494

I think your layout using just one table is fine. You definitely want to keep SIRE_ID and DAME_ID in the same data type as ID. You also want to declare them as FOREIGN KEYs (it is possible to have a foreign key point back to the same table, and a foreign key can also be null).

ID INT NOT NULL PRIMARY KEY
SIRE_ID INT REFERENCES TABLENAME (ID)
DAME_ID INT REFERENCES TABLENAME (ID)

Using this layout, you can easily look up the parent animals, and you could also build an offspring tree for a given animal (for Oracle there is CONNECT BY)

Upvotes: 4

tvanfosson
tvanfosson

Reputation: 532435

I think that since it is clear that an animal only has one sire and one dam, that using a single table would make the most sense. My preference is to use int or bigint as the row identifier, with a null value signifying no relationship. I would probably, then, to use some other method to uniquely identify animals so they don't end up in the table twice and create a unique index on that column as well.

Upvotes: 0

Mac-E
Mac-E

Reputation:

It's not really a one to many relationship, unless an animal can have many parents.

I would leave it as a single table with the unique key ID for the animal, one int field for each of the parents, and probably a text field to use for general notes about the animal, like where it was purchased if that's the case.

Upvotes: 0

dacracot
dacracot

Reputation: 22348

Use the "connect by" clause with SQL to tell it which hierarchy to follow.

Upvotes: 0

Noah Goodrich
Noah Goodrich

Reputation: 25263

I asked a similar question a number of months ago on the MySQL website. I would recommend that you take a look at the response that I received from Peter Brawley regarding this type of relationship: http://forums.mysql.com/read.php?135,187196,187196#msg-187196

If you want to research the topic further then I would recommend that you look into Tree Hierarchies on Wikipedia.

An alternate suggested architecture (that would be fully normalized) would look something like the following:

Table: animal

ID | Name | Breed

Table: pedigree

animal_id | parent_id | parentType (either sire or dame)

Upvotes: 3

Lost in Alabama
Lost in Alabama

Reputation: 1653

INT is the better choice for the ID column and better suited if you should use a sequence to generate the unique IDs.

I don't see any benefit in splitting the design into two tables.

Upvotes: 1

millenomi
millenomi

Reputation: 6589

Well, this is a "normal" one-to-many relationship and the method you suggest is the classical one for solving it.

Note that two tables are denormalized (I can't point out exactly where the superkey-is-not-well-should-be-subset-of-other-key-fsck-I-forgot part is, but I'm pretty sure it's there somewhere); the intuitive reason is that a tuple in the first one matches at most a tuple in the second one, so unless you have lots of animals with null sire and dame IDs, it's not a good solution in any prospect (it worsens performance -- need a join -- and does not reduce storage requirements).

Upvotes: 6

Related Questions