Solved

mySQL table FK (relationship) problem

Posted on 2011-03-02
5
450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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 …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

688 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