Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 7 of 7
  1. #1
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Storing Hierarchical Data in a Database
    Converting an adjacency list model to a nested set model

    Is this truly the best way to add/reference data in databases? I seriously doubt that's what BBs are using nowadays, or are they?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  2.  

  3. #2
    Member
    Join Date
    Oct 2003
    Location
    california
    Posts
    32
    Member #
    3452
    I don't know if it's "truly the best way to add/reference data in databases" but I sure am using the second method detailed in the first link (Modified Preorder Tree Traversal). I think it makes a lot of sense and is easy to work with.

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    That's the method I was telling you about here: http://www.webdesignforums.net/threa...tml#post100934

    The Left / Right pairing allows faster indexing and searching in parent/child relationships, so that you don't need to do recursive querying to find relationships that span multiple generations.

    It's a very good way for storing single-parent entities in databases, but cannot be used to store two-parent relationships, because you would necessarily have overlap between Left / Right pairs (unless an enforcement on the relationships is that one entity can only ever have one mate).

    For that reason, this architecture is not suitable for your breeding problem. You could, however, probably devise a derivative of the Left / Right pairing that would allow you to correctly structure two-parent-child relatioships with fast indexing.

    Let me know if you succeed. ;-) If not, I'll think of ways of doing it myself and let you know if I think of anything.

  5. #4
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Oh THAT's what that was! I can see it now lol.

    If you wanted multiple parents, you could hack it to become one. Every unique pairing of parents would result in a unique parent_id, which would then virtually replace the parents. Children would reference the parents that way.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  6. #5
    Member
    Join Date
    Oct 2003
    Location
    california
    Posts
    32
    Member #
    3452
    You could also create another table, a lookup table if you will.

    (Using my application as an example.)

    products:
    id
    name

    categories:
    name
    lft
    rgt

    products_categories:
    prod_id
    c_lft
    c_rgt


    That's pretty much it. Allows me to put a product into as many categories as I want.



    Chris.

  7. #6
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Transio, is that stuff efficient in mySQL, because if you have unique IDs in each entry, you don't need to search?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    i'd say something more like what you suggested... with the litter <sp> having a unique id... that way, you can also keep track of which animals came from the same litters, as well. Something like this would do well for you:
    Code:
    CREATE TABLE animals (
        id int NOT NULL AUTO_INCREMENT,
        litter_id int NULL REFERENCES litters(id),
        breed_id int NULL REFERENCES breeds(id),
        gender_id int NOT NULL REFERENCES genders(id),
        name varchar(255) NOT NULL
        PRIMARY KEY (id)
    );
    CREATE TABLE litters (
        id int NOT NULL AUTO_INCREMENT,
        father_id int NULL REFERENCES animals(id),
        mother_id int NULL REFERENCES animals(id),
        left int NOT NULL,
        right int NOT NULL,
        mating_date datetime NULL,
        birth_date datetime NULL,
        PRIMARY KEY (id)
    );
    If you look at it in this context, though, two things become apparent:
    1. The left/right pairing won't work, because it's spanning two sets of meta-data.
    2. You have a circular reference with the litter / parents... which is not a recommended architecture.

    Needs some more work... keep thinking about it.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 07:45 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com