Solved

mySQL table FK (relationship) problem

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

20 Experts available now in Live!

Get 1:1 Help Now