please help me understand one-to-many

rbfree · #1 (**permalink**) 02-06-09, 17:02

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?

r937 · #2 (**permalink**) 02-06-09, 18:43

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

rbfree · #3 (**permalink**) 02-06-09, 19:29

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)?

r937 · #4 (**permalink**) 02-06-09, 20:25

1. yes
2. yes

rbfree · #5 (**permalink**) 02-07-09, 11:32

....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.

r937 · #6 (**permalink**) 02-07-09, 13:01

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

Internet Services

Web Development

Digital Marketing

Consumer Tech