Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Unanswered: Parent/Child/Grandchild DB Design

    Hi everyone,

    I am having a design fart and could use some help.

    I am tracking equipment and I essentially have three tiers. I have a parent, a child, and a grandchild. Each of these are pieces of equipment and they all have the same metadata associated with them. For simplicity sake, let's say that each piece of equipment has a name and a description. I am trying to use proper database design, and I am seeing something that looks troubling.

    Currently, I have a parent table with the equipment name and a description. I also have a child table with a parent ID (w/ 1-many), equipment name, and description. And finally, I also have a grandchild table with a childname (1-many to the childID--note that htis is the child of the parent, not the grandchild) and a description.

    In my experience, having any kind of repeating data is not good practice, but I don't know how to separate things out. I would love to just have one table that has all of the equipment and then just say if it is a parent, child, etc. and list what it is, but how would you reference that from the same table? I thought of having a metadata table to house all of the meatadata and then have a "Type" column with it to say if it is a parent, child, etc, but then how would I properly reference the parent data and associate them together? Keep in mind too, while each parent, child, etc has the same metadata fields, the metadata for a parent would be different than that of the child (parent has a different description than child, etc.). My brain hurts

    Appreciate any and all input. If you need any clarification, please let me know. Thanks.
    Last edited by pdevito3; 06-18-14 at 15:40.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're over thinking the problem... One table for equipment, a second table for relationships which are basically the PK values for two related pieces of equipment (along with anything else you need to track regarding the relationship). This allows you to keep the equipment quite stable, and the relationships are simple since each row just identifies a piece of equipment and its immediate ancestor.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    clarification

    Interesting. I think I know what you are saying. When you say "one table for equipment and one for relationships", do you mean:


    table 1
    equipmentID
    equipment name
    metadata 1, 2, n...

    table 2
    relationshipID
    Parent
    Child
    Grandchild

    where equipment ID would go to any of the fields as a PK?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Close. Leave the grandchild out of the second table, then use two rows.

    In the case where Charlene is Barbara's daughter and Ann's grand-daughter, you would have one row showing Charlene as Barbara's daughter and a second row showing Barbara as Ann's daughter. This can be repeated for as many generations as needed.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Great. Much appreciated!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •