Solved

mySQL table FK (relationship) problem

Posted on 2011-03-02
5
447 Views
Last Modified: 2012-05-11
Experts,

I need to make a foreign key which will link four different types of oilfileld equipment.

The main table is called equipment, with among other things, two columns: equipment_typeFK and equipment_linkFK.  These two when combined are unique.

I need to make relationships with other entities -- well, pipeline, satellite and facility.-- which are all different types of equipment.  Can I not link their primary keys to the two fields of the equipment table?   (MySQL doesn't like it so far.)

Please advise,

Capt
0
Comment
Question by:Glen Gibb
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
asidu earned 250 total points
ID: 35024746

Why dont you join the two columns into one extra column for the purpose of FK.

So you can have three columns in all in the table equipment as equipmen_typeFK, euipment_linkFK, and a new field euipment_typelinkFK.

0
 
LVL 4

Assisted Solution

by:gizmola
gizmola earned 250 total points
ID: 35025465
I would really need more information about the table structure and nature of the columns, as well as what you're trying to accomplish.  For example, you state that your equipment table has equipment_typeFK and equipment_linkFK.  What is the nature of each of these columns?  I can guess that the equipment_type table is a list of different types of equipment, so that you can describe a particular equipment row as being of one equipment_type?  What then would equipment_linkFK be, and why would the combination of type and link be unique?  

Why are these other tables seperate?  Why don't you instead just have rows in the equipment table, with the equipment_type set for each?  

Does equipment not have a primary key?

Why are you trying to relate a row in equipment with these 4 other tables, and what is the purpose of that relationship?
0
 

Author Comment

by:Glen Gibb
ID: 35026436
To add to the setup info as requested,
1.  The equipment table certainly does have a PK.
2.  The idea is to separate the common items of equipment into one table (who owns it, where it is, etc).
3.  Then the separate tables have unique columns identifying their special properties.

Doh!  I just put an equipmentFK in the separate tables and link back to equipment!  The light dawns!

Tnx,

Cap
0
 

Author Closing Comment

by:Glen Gibb
ID: 35026449
Sometimes you're just up too late!
0
 
LVL 4

Expert Comment

by:gizmola
ID: 35040042
Ok, so I think I understand that what you were looking for is typically called sub-typing.

So yes, your equipment table has the common attributes for a piece of equipment and each subtype table will have the special columns needed for that "type" of equipment.

The problem with this relationally is that there is no intrinsic SQL support for it, but the design of having the equipment table PK as the Foreign - PK in each of the subtype tables is the way to go.

You can help out with allowing to write somewhat generic frontend code, if you use a naming convention for each of the equipment subtype tables that matches your category.

So for example, if you category 3 = pipeline then name your pipeline table:

equipment_pipeline.

Then your model code can intelligently determine what table to join to when you need all the row data for a particular piece of equipment.

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question