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
Solved

mySQL table FK (relationship) problem

Posted on 2011-03-02
5
448 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 178
Mysql Crashing Intermittently 16 114
showing numeric numbers 2 38
Sql query on a varchar that is numeric. 8 44
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

838 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