If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Creating a Foreign Key cross-reference query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Aug 2010
Posts: 57
Creating a Foreign Key cross-reference query

In my database I have a table that is used to store document references - namely the source of the data being captured in all other tables. I call this table "InfoSource". Thus each table in the database may have multiple foreign key references to an entry in "InfoSource".

For instance, in a table called "person" I may have the following fields: "birthday", "weight", "height", "favorite color". For each field I would have an additional field "birthday_infosource", "weight_infosource" etc. that is a foreign key to an entry in "InfoSource".

Now I would like to create a cross reference. Given a record in "InfoSource" which records across all other tables point to it?

Right now the way I do this is via a query with a bunch of unions, where I query each individual table. The drawback is that every time a new table is added or a table is changed, I have to update the cross-reference query.

Does it make sense to implement this via a stored procedure? In pseudo code I can think of something like this:
Code:
for each table
for each field with "InfoSource" in name"
select record from InfoSource referenced by foreign key field
Unfortunately, I have no idea how I'd begin to even implement this in a SP. I'm open to suggestions.

Thanks!
Carlos

Last edited by carlosn; 01-07-13 at 11:29. Reason: Fixed spelling errors :)
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,789
Granted that I'm an odd duck and my databases all have a common surrogate key structure (the keys aren't all exactly alike, but they are all single-column and one datatype). This would allow me to build one table to track usages (essentially identifying a column within a table), and then a simple table that tracked that usage along with a PK (Primary Key) value (from the row) and to associate that with a given InfoSource row (PK). Then it becomes a simple matter to index the association table and query to my heart's content!

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,200
Quote:
Originally Posted by carlosn View Post
In my database I have a table that is used to store document references - namely the source of the data being captured in all other tables. I call this table "InfoSource".
personally I call that an EAV type design, and it nearly always results in a nightmare. So much of so I avoid it like the plague
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Aug 2010
Posts: 57
Quote:
Originally Posted by healdem View Post
personally I call that an EAV type design, and it nearly always results in a nightmare. So much of so I avoid it like the plague
OK - you caught my attention. Why would you say this is an EAV type design?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2012
Posts: 39
Data Model for EAV

Hi

This EAV Data Model omn my Database Answers Web Site might help you :-
User-Defined Hierarchies Data Model

HTH

Barry Williams
London, England
Reply With Quote
  #6 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
Yes, it's an EAV design.
Yes, EAV should be avoided.

Consider using an XML datatype to store dissimilar fields, if your platform supports it.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On