One for many

Question

How to structure a table for an object that may relate from one to itself? In particular, I am working on a livestock tracking application. Each animal has an identity card; he also received a sire id and a lady id. Thus, he can have from one to many from his father or lady to his offspring. I would be inclined to something like this:

ID INT NOT NULL PRIMARY KEY SIRE_ID INT DAME_ID INT 

and write down the zero value for those animals that were acquired and added to the breeding cattle, and the identifier in the table for the rest.

So:

• Can someone point me to an article / webpage that discusses modeling this kind of relationship?
• Should the identifier be int or somehow string? NULLs in INT indicate that the animal does not have parents in the database, but a string with special flag values ​​can be used to indicate the same.

• Perhaps this will be best modeled through two tables? I mean one table for animals and a separate table showing only kinship e. g :.

  Animal

  ID INT NOT NULL PRIMARY KEY

  Similarity

  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. Hope this looks like what I'm thinking of.

Answer 1

Well, this is a “normal” one-to-many relationship, and the method you offer is classic for solving it.

Note that the two tables are denormalized (I cannot specify exactly where the part of the superclass is - it is not-well-should be a subset of the other -key-fsck-I-forgotten, but I'm pretty sure there somewhere); the intuitive reason is that the tuple in the first corresponds to at least the tuple in the second, so if you don’t have a large number of animals with type zero and lady identifiers, this is not a good solution in any perspective (it affects performance - connection is required - and does not reduce storage requirements).

Answer 2

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

If you want to continue exploring the topic further, I would recommend that you study tree hierarchies on Wikipedia.

An alternative proposed architecture (which would be completely normalized) would look something like this:

Table: animal

ID | Name | Breed

Table: pedigree

animal_id | parent_id | parentType (either sire or dame)

Answer 3

I think your layout using only one table is ok. 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 that the foreign key point returns to the same table, and the foreign key may 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 find the parent animals, and you can also build a tree of offspring for this animal (for Oracle there is CONNECT BY)

Answer 4

INT is the best choice for an ID column and is better suited if you must use a sequence to generate unique identifiers.

I see no advantage in splitting a design into two tables.

Answer 5

I don’t know about livestock, but it looks like your Sire_ID is the father and Dame_ID is the mother? No problems. One line for each animal, null sire_ and dame_ID for acquired animals, I do not see 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 

etc. You are probably populating 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 will return myAnimal..Parent [] can be a collection of his two parents, which should work as long as [0] is always one parent (father), and [1] is always different (mother) .

Make the caller ID PK and assign Sire_ID and Dame_ID programmatically, returning the identifiers of your parents. No relationship with foreign keys should be required, although both parent identifiers can refer to IDs if you really want to.

Answer 6

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

Answer 7

This is not exactly one relationship if an animal cannot have many parents.

I would leave it as a separate table with a unique key identifier for the animal, one field for each of the parents and, possibly, a text field that will be used for general notes about the animal, for example, where it was bought, if this is the case.

Answer 8

I think that since it is clear that the animal has only one sire and one dam, using a single table will make the most sense. My preference is to use int or bigint as the identifier of a string with a null value meaning no relationship. I would probably use another method to uniquely identify animals, so they don’t get into the table twice and create a unique index in this column.

Answer 9

It looks like you want to build something like a tree.

How about something like ?:

  ID Primary Key, Parent_ID Foreing_Key ( data ) 

There is some functionality for queries in tables with relationships to themselves. See Connect Syntax: http://www.adp-gmbh.ch/ora/sql/connect_by.html