Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    378
    Thanks
    9
    Thanked 39 Times in 39 Posts

    Self referencing table or not?

    Hi there,

    I am creating a DB for an application that has components called packages, folders and pages. They have slightly different properties but some are the same. Some types can be "contained within" other types.

    I am wondering if it is better to have one table for each type (package, folder, page) or have one table with a type_id and a parent_id.

    Which do you think will be quicker for a select statement?

    e.g. (SELECT from packages UNION SELECT FROM pages UNIO....) in the first case OR (SELECT FROM components c1 LEFT JOIN components c2 ON c2.parent_id...) in the second instance.

    Thanks.
    Regards, Stooshie
    O

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I've worked a lot with tables that store a more generic "object" type thing, so the rows in the table are mostly defined by relationships with other rows in that same table... and it does have its advantages. But it can also be a royal pain in the rump debugging problems.

    If you have no real compelling reason to organize the data this way, for clarity's sake I would create separate tables for each object type.

    When it comes to speed of data access, your two examples don't make sense to me. One is a union and one is a join, but wouldn't your different object types interact with one another in the same ways (either unioned or joined) regardless of which design you chose? If I'm right then the real question should be: Is joining a table to itself faster or slower than joining two different tables? And my answer is... I dunno... set up tests of both scenarios and find out.

  • Users who have thanked Fumigator for this post:

    Stooshie (05-23-2011)

  • #3
    Regular Coder Stooshie's Avatar
    Join Date
    Mar 2008
    Location
    Dundee, Scotland
    Posts
    378
    Thanks
    9
    Thanked 39 Times in 39 Posts
    Thanks Fumigator.

    The reason I did a join in the one case was I wanted all the items from each table with the same parent id. And in the second instance (if they were all in the same table) I neede to do a join. Maybe a join in the first instance would be better anyway!

    I will set up a test and see.

    Thanks again.
    Regards, Stooshie
    O


  •  

    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
    •