Please excuse the n00b question. If it's off topic or should go elsewhere, please let me know. Thanks.

I'm trying to call a parent table into one than one column in a table. My dilemma is whether to 1. use alias names for foreign keys in multiple fields in the same table or 2. build a small table corresponding to each column I want in the child table, enable me to indirectly link the parent table in question. (The parent table would become the grandparent in the 2nd approach.)

I'm facing this dilemma in several circumstances.


As an example, in a child table named transportsystems, I have a primary key (transportsysID) and a foreign key (equipmentID), which is the primary key to a table named equipment.

Each record in the transportsystems table describes a transportation system. Each transport system might use any number of pieces of equipment. For the sake of understanding this problem, I'll say from 1 to 2 pieces of equipment.

In transportsystems, I need two columns -- each one corresponding to a piece of equipment. I need to be able to call in the equipmentID twice into the same table -- so I can perform operations on the attributes. (For instance, I want to be able to add up the fuel use, in gallons per acre, so I can say how many total gallons per acre I'm using for a given tranportation system).

To me, the simplest approach would be to use 2 alias keys (say, equipment1ID and equipment2ID), since I can't use equipmentID twice in one table. However, at least one text I'm reading claims that one should rarely use this approach.

So, the other solution that occurs to me is to build a small table corresponding to each column that I want in transportsystems table, with table names equipment1 and equipment 2 and corresponding primary keys, equipment1ID and equipment2ID. Then, I can call the columns equipment1ID and equipment2ID into the transportationsystems table, thereby indirectly calling in the column equipmentID from the equipment table.

Which is the better approach? Thanks in advance!

the first approach is better -- join to the equipment table twice, with aliases

Thanks r937.

May I ask you to give me a brief explanation? I'm definitely not trying to be impetuous, but rather, trying to get a solid understanding of how database design works with physical structure, etc. (what works and why).

Again, thanks.

If a transport system may use "any number of pieces of equipment", then you should not be defining multiple equipmentID columns in the transportsystems table. Instead you want an "intersection" or "many to many" table, like this one:

You can then query:

If there are really only ever a maximum of 2 pieces of equipment per transport system (e.g. if a transport system is defined as a set of 2 pieces of equipment, like a line can be defined as a pair of points) then 2 columns is acceptable. But not if you think you'll ever need to add an Equipment3ID.

I think I'm seeing the problem, however, in my communication. The equipment table will have a record for each brand and model of equipment -- not each physical piece of equipment.

So, in the equipment table, maybe equipmentID number 22 will correspond to a "Gehl 6640E," which generally uses 20-gallons/acre, and has 80-horsepower, and functions on a maximum slope of 30-percent. (I'm faking these values.) This record with primary key=22 is unique. Maybe another record (primary key=33) will correspond to a "Caterpillar 320C fm forwarder," with z-gallons/acre, z-horsepower, etc. -- another unique type of equipment. Primary key 11 could represent the case of no equipment (with 0-gallons/acre, and null horsepower and null max slope, etc.

However, any given transportation system might use, say, 1 of the gehls and one of the caterpillars. Thus, in a given record (a given trans system), in field transportationsystems.equipment1, I would have FK=22, and in .equipment2, I would have FK 33.

And, another transportation might use two gehls and no cats. So, in a given record (a given trans system), in field transportationsystems.equipment1, I would have FK=22, and in .equipment2, I would have FK=22.

Maybe another trans system would use one cat and nothing else. So, in a given record (a given trans system), in field transportationsystems.equipment1, I would have FK=33, and in .equipment3, I would have FK = 11.

To know which types of equipment a transport system employs, don't I need to have fields made for those values? How else would these different and unique transport system be attributed the equipment they use? Wouldn't I need a field to enter each piece of equipment?

Also, the relationship between the equipment.equipmentID (parent) and the transportationsystem.equipmentID (child) is one-to-many from the perspective of the parent table (equipment). In otherwords, one record in equipment.equipmentID can correspond to one or many records in transportationsystem.equipmentID. (Any transportation system can have several identical pieces of equipment.) However, one record in transportationsystem.equipment ID can only correspond to one record in equipment.equipmentID. (An identifier for a piece of equipment used in a transportation system can only correspond to one piece of equipment in the equipment table -- since they're all unique.)

As you said, any given transportation system can have any combination of equipment -- (and I mean combination in the strict sense) -- ranging from one piece to many (though for the example, I'm saying 1 or 2 pieces of equipment).

I need a column/field in my transportationsystem table for each possible piece of equipment (2 for this example, up to 20 or beyond in real life).

For each unique transportation system (identified by transportsysID in the tranportation file), only one record per column is possible -- that is, only one instance of equipment can be represented in a field in one record. (No multi-value fields for reasons of referential integrity!) But, as I noted, I need to be able to call more than one piece of equipment for each transportation system (each unique record). So, I require more than one column. With one column available for each piece of equipment that might be used (sometimes, two of the same piece is part of a transport system), I can have a record that shows each piece of equipment involved. These pieces of equipment are attributes of the transportation system.

no prob


so now do you understand that each additional column is an additional INNER JOIN in the query? let me ask you -- how do you feel about constantly having to change your queries to add another join when you run across a new transport system that requires one more piece of equipment than you have columns for

what you actually have -- and which you explained in lovely detail -- is a many-to-many relationship

do some googling

you're almost beyond the n00b stage, but not quite

as soon as the many-to-many coin drops, you'll see how everything becomes simple

I'll take your advice and looking into/ruminate upon this many-to-many issue... and also look into the INNER JOINS.

The many-to-many issue has me scratching my head!

see also first part of post #5 where the many-to-many table that you need is described