Solved

mySQL table FK (relationship) problem

Posted on 2011-03-02
5
443 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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
Sometimes you're just up too late!
0
 
LVL 4

Expert Comment

by:gizmola
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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 …
Creating and Managing Databases with phpMyAdmin in cPanel.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now