Post reply

Father Son Relationship

  • August 6, 2009 at 6:59 am

    #209094

    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

  • August 6, 2009 at 7:34 am

    #1035352

    i'd have them in one table and self reference as a son could also be a father. presumably.

  • August 6, 2009 at 7:42 am

    #1035355

    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?

  • August 6, 2009 at 8:24 am

    #1035389

    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.

  • August 6, 2009 at 10:04 pm

    #1035791

    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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply