0

I have a table with 3 columns id,Item,parentid when storing data the top most level will have a parentID of 0 while the second level will have ParentID of 2 (relative) which will be an id an item.

 ----------------------
 id | Items    | parentId
 ---------------------- 
  1 | Computers|    0
  2 | Dell     |    1
  3 | laptop   |    2

Is this the best design considering the levels might be endless.

1
  • 1
    One note that comes to mind. Make ParentID nullable and set it to NULL, rather than 0 for the top level. In this case you can set up a foreign key on a table pointing to itself. Nov 10, 2015 at 0:42

2 Answers 2

2

The question of whether or not this is the "Best Design" is really more of a philosophical question that needs to have more information explained. If you are looking to merely manage a simple hierarchical set of items, then this is good design.

You're definitely going to have to be very mindful of referential integrity if items are being added deleted in the various layers of the parent-child relationship.

2
  • Thanks, by best design i meant in terms of performance. In case it spans to the 10th level with thousands of records.
    – love
    Nov 9, 2015 at 20:38
  • 1
    @love Still not enough information. Performance of what? Reads? Writes? Everything? How often is the data read, vs. updated, vs. shifting between parents or levels? There are definitely multiple ways to represent hierarchy in a relational database; most of us won't be able to spitball which one works best for your scenario with any confidence. Nov 9, 2015 at 20:49
0

I can't post comments yet, but I'd like to add to Alan and Aaron's answers. I agree that you haven't provided enough information.

Is hierarchy the only thing you need to demonstrate? Will you ever have to store other information about the items in your table? Will you need to have rules for what is allowable in the hierarchy? (e.g. Are circular references allowable? Do you need to code those restrictions?)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.