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.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18
  1. #1
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    A friend is looking into cataloging the pedigrees of a certain species of animal. Has anyone ever done this before?

    ATM I'm sketching out rough ideas. My idea is that when someone enters in the unique ID of an animal, it will come up with their basic bio info. In that, among other things, will be a list of all of its mates. Next to the mate's name will be a link to the list of the children. I think this will work well, but can build up quickly. Does anyone mind checking out the basics for me?

    Animal_Table
    {
    Animal_ID
    Mates_Table_ID
    }

    Mates_Table
    {
    Mates_Table_ID
    Mate_ID
    Children_Table
    }

    Something's wrong with the above sketch, but I'm too damn tired to see it And yes, I know it's not in normal form, but you can attribute that to the lack of sleep as well.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  2.  

  3. #2
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    Got to run to work, but it seems to me that what you need is quite simply...

    Animal
    {
    Animal ID
    Lots of other information such as name etc.
    .
    }

    Relationship
    {
    Relationship ID
    Relationship Name -- ie. Child, Mate etc.
    }

    Animal Relationship
    {
    Animal_ID 1
    Animal_ID 2
    Relationship ID
    }

    ...in a bit of a rush, but hopefully will help form the genesis of an idea.

    Sean

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Well, i believe that pedigrees are a heirarchical relationship... meaning that it's completely a parent/child relationship between breeds (at least from charts that I've seen in the past). This would only require one table with a recursive architecture to correctly organize your breeds... like so:

    Code:
    CREATE TABLE breeds (
        id int NOT NULL AUTO_INCREMENT,
        parent_id int NULL REFERENCES breeds(id),  //recursive key
        species_id int NOT NULL REFERENCES species(id),  //optional
        name varchar(255) NOT NULL
        description text NULL,
        PRIMARY KEY (id)
    );
    So now the parent_id column contains a reference to the id column. It is nullable so that top-level breeds are allowed to have no parent (wolves, I imagine).

    Unfortunately, working with data like this is more complex than in multiple tables, often requiring sub-queries or business logic to process, which may stump MySQL. Nevertheless, it's your best bet for table design.

  5. #4
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Transio's right; a resursive structure would be the best way to go, although it can screw with your mind like nothing else. If you have any forum software that is purely multithreaded, try looking at its code. A purely multithreaded forum requires a hierarchy to operate and so would your project.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  6. #5
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    I did not read the original requirement as being relationships between breeds, rather relationships between individual animals...

    ...in which case a child animal will have multiple parents (well, that's how I was taught that breeding worked at least) and therefore your table structures will not work. Similarly, a parent will have multiple children (unless they go sterile after the first).... many to many relationship -> more than one table required.

    Sean (hopefully learnt something through 17 years working in the RDBMS industry!)

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Well, mammals being sexual beings, we only have two parents.... a father and a mother. I don't think this will ever change, so you could still use a recursive relationship for your scenario... just with TWO recursive keys, like so:
    Code:
    CREATE TABLE animals (
        id int NOT NULL AUTO_INCREMENT,
        father_id int NULL REFERENCES animals(id),  //recursive key
        mother_id int NULL REFERENCES animals(id),  //recursive key
        breed_id int NULL REFERENCES breeds(id),
        name varchar(255) NOT NULL
        PRIMARY KEY (id)
    );
    You could still have the breed table referenced if you want, so that you can determine the exact breed of an animal, including cross-breeds (by parental breed lookups)

  8. #7
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Ok, what about a list linked to each animal showing its mates? Each "coupling" would spawn one or more children.

    What I'm thinking of (and please correct me if I'm going off on a crappy design tangent) would be that the animal table would include a sub-table of mates, and another sub-table of children. Don't know how efficient this idea would be, but here it goes. Once someone pulls up the animal, it could delve into each of the children's parent ids and organize the children next to the mate that they are attached to. Basically dynamic and all that.

    On a separate not, can anyone recommend a good database book? I used to really like this stuff, but can't get my head around it anymore, it's been 4 yrs or so.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Not necessary. A mate is only appropriate in the context of spawn.

    For that purpose, you can do a query like so to get a list of mates by an animal's PHP $id:

    For males:
    Code:
    SELECT
        parent.name AS mate, 
        child.name AS spawn
    FROM animals AS parent
    INNER JOIN animals AS child ON
        ON child.father_id = $id
            AND child.mother_id = parent.id
    For females:
    Code:
    SELECT 
        parent.name AS mate, 
        child.name AS spawn
    FROM animals AS parent
    INNER JOIN animals AS child ON
        ON child.mother_id = $id
            AND child.father_id = parent.id
    This will give you a list of mates and spawn produced for your animal. I think these are the only appropriate mates in this context... I personally don't remember every girl I slept with... but I think I would be able to recall the ones who spawned children for me !!!

    You might want to store gender in your "animals" table to have more appropriate meta-data, like so:
    Code:
    CREATE TABLE animals (
        id int NOT NULL AUTO_INCREMENT,
        father_id int NULL REFERENCES animals(id),  //recursive key
        mother_id int NULL REFERENCES animals(id),  //recursive key
        breed_id int NULL REFERENCES breeds(id),
        gender_id int NOT NULL REFERENCES genders(id),
        name varchar(255) NOT NULL
        PRIMARY KEY (id)
    );

  10. #9
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    For efficiency and query time, would the above be easier, or would something else be easier for a potential DB with say 1 million entries?

    Just found out that finding the mates won't be that important, nor who their children are. Just parental info to prevent inbreeding, which will be pretty easy. Now the hard part will be user logins, linking it to a pay system (just found out this will make money lol).
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  11. #10
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Do I get royalties for db architecture? ;-)


Page 1 of 2 1 2 LastLast

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:30 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com