|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Thursday, December 24, 2009 5:43 AM
Points: 38,
Visits: 179
|
|
Hi I am after some advice
I have to record Father and Son details using sql 2008.
would you have the information all in one table or have two tables with the fathers in one and sons in other table
all advice is welcome
thanks
Fred
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 10:38 AM
Points: 122,
Visits: 445
|
|
| i'd have them in one table and self reference as a son could also be a father. presumably.
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Friday, January 22, 2010 8:01 AM
Points: 1,070,
Visits: 2,036
|
|
A few questions before giving definitive advice...
Will you be recording EXACTLY the same sort of information (e.g. name, address, dob) for Father and Son, or will one have more/less information than the other (e.g. only record Marital Status for Father)?
Can a Son have only one father (not biologically!!) e.g. would you need to record both a natural father and a stepfather.
Can a Son exist without a Father?
Can a Son also be a Father?
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Tuesday, October 27, 2009 9:14 AM
Points: 13,
Visits: 77
|
|
Are we talking 'metaphorical' father and son relationships here, as in the nodes in a graph that may be described as 'parents' and 'children', or do you really mean a list of fathers and their sons? (and not their grandsons as well?)
Essentially, the answer depends on whether the data you are modelling conforms to the rules of a hierarchy (i.e. it has a single root node, every child must have a parent, and the graph is directed), or whether you are just describing a general graph where nodes can be connected to each other, but without any further constraints.
Options that spring to mind are:
- A father table and a son table, joined on keys.
- Father and son records stored in the same table, using adjacency lists to reference between them (similar to how employee tables typically structure employee/manager relationships)
- The hierarchyid datatype
Without further information though, it's hard to recommend which is most suitable for your situation.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, December 24, 2009 5:43 AM
Points: 38,
Visits: 179
|
|
thanks for the response.
The sons will have different information to the fathers .
and the sons wont be a father.
I am leaning towards having 2 tables
a Father table and a Son table as a father can have more than one son..with the links between the two
thanks
Fred
|
|
|
|
