Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    please help me understand one-to-many

    Please excuse this repetitive attempt to gain an understanding. (I appreciate your patience, especially you, r937!!) I need to break my misunderstanding into two parts. This is the first. Please bear with me.

    Please refer to the attached pdf file with simple table.

    As a manager, I want to be able to answer this question: what specific pieces of equipment are involved with any given harvest system? So, if I have three harvest systems in my set of harvest systems, I want to be able to look up one of them and see exactly what equipment it requires. In addition, I want to know some characteristics about each of these pieces of equipment, based on general information about each brand and model -- like how much fuel per hour each type (brand and model) uses.

    So, If I want to know what pieces of equipment harvest system number 2 (harvestsysID="2") uses, I can look it up and see that it uses a Bigskidster Mauler -- a brand and model of equipment -- as well as a Destructo Skidder. Likewise, I can see that harvest system 3 uses a Bigskidster Mauler, a Destructo Skidder, and a Minitruck Forwarder. Furthermore, I can then find out how much fuel each piece of equipment uses per hour... and thus, how much the whole harvest system uses per hour... and through my link with the fueltypes table, kbtuspergallon field (fueltypes.kbtuspergallon), how much energy each piece of equipment... and each harvest system uses.

    I've looked at many definitions for one-to-many relationships, and have been referring to several bought texts.

    EG. One-to-Many Relationships in Databases. Definition: "One-to-many relationships occur when each record in TableA may have many linked records in TableB but each record in TableB may have only one corresponding record in TableA."


    From my understanding, the relationship between equipmenttypes.equiptypeID (parent) and harvestsystems.equiptypeID (child) is a one-to-many relationship, because of two conditions:
    1. a single record in equipmenttypes.equiptypeID can correspond to any record in harvestsystems.equiptypeID.
    2. one record in harvestsystems.equiptypeID can ONLY correspond to one record in equipmenttypes.equiptypeID, since it is a primary key, and thus each record value is unique.

    For example, in the harvestsystems table, in the equipmenttypeID field, in the record/row harvestsysID="1" , we know that this value, "1," which denotes the Bigskidster Mauler (in the equipmenttypes table), only has one match or corresponding record in equipmenttypes.equipmenttypeID.

    However, in the equipmenttypes table, in the equipmenttypeID field, the record equipmenttypes.equipmenttypeID=1 (Bigskidster Mauler) can be found only once (since it's unique), while in the harvestystems.equipmenttypeID, it can be found twice.

    So, where is my thinking incorrect?
    Attached Files Attached Files
    Last edited by rbfree; 02-06-09 at 18:06.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your understanding of one-to-many relationships is fine

    however, your harvestsystems table is wrong (it's not in first normal form) because it has 3 FKs, and as each of them points to the same equipment type table, what you really have is a many-to-many relationship

    this requires three tables -- equipmenttypes, harvestsystems, and an association or many-to-many or linking table which i will call harvestsystems_equipment

    you need to remove the "dummy" entry with PK=0 from the equipment types table

    the other two tables in yout many-to-many relationship should look like this --
    Code:
    CREATE TABLE harvestsystems  
    ( harvestsysID INTEGER NOT NULL PRIMARY KEY 
    , name VARCHAR(99)
    );
    INSERT INTO harvestsystems VALUES
     ( 1, 'system number one' )
    ,( 2, 'system number two' )
    ,( 3, 'system number thr' )
    
    CREATE TABLE harvestsystems_equipment
    ( harvestsysID INTEGER NOT NULL 
    , equipmenttypeID INTEGER NOT NULL
    , PRIMARY KEY ( harvestsysID, equipmenttypeID )
    );
    INSERT INTO harvestsystems_equipment VALUES
     ( 1 , 2 )
    ,( 1 , 3 )
    ,( 1 , 4 )
    ,( 2 , 1 )
    ,( 2 , 3 )
    ,( 3 , 1 )
    ,( 3 , 3 )
    ,( 3 , 5 )
    ;
    notice that no row exists for the relationship of any harvester to an equipment type which doesn't exist (i.e. where you had a 0, that row isn't needed)


    let me know if you have further questions

    Last edited by r937; 02-06-09 at 19:47.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    104

    ok, another question please?

    OK, thanks again. I think you cracked through my thick skull this time.

    1. This, then creates a compound primary key in harvestsystems_equipment table, right?
    PRIMARY KEY ( harvestsysID, equipmenttypeID )

    2. Does this assume that the equipment table already exists (with PK equipmentID)?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. yes
    2. yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2009
    Posts
    104

    I get it

    ....This strategy provides a place to have the data... and then, I keep a query/view to perform my calculations/analytics.

    I'm seeing that the queries are more powerful than I thought, and play a bigger part than I thought.

    Also, I'm seeing that I had a subtle misunderstanding of many-to-many (or one-to-one), in that I thought the relation was only between two fields (in two different tables, of course), but it's actually between tables. My relation between e.equipmentID and h.equipmentID, _is_ one-to-one ... just as it's one-to-one between e.equipmentID and h.equipment2ID, or h.equipment3ID. But the relationship between e.equipmentID and the _set_ of h.equipmentID fields is many-to-many.

    Thanks for the nudge! Wow, this knowledge will significantly slim down my database... and it will help me work through a few more knots. Very helpful. I really appreciate your help.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words

    it is nice when the light goes on, isn't it

    i still get that from time to time too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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