Results 1 to 6 of 6
  1. #1
    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 :)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,902
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,411
    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 on the Tiger 800 or the Norton

  4. #4
    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?

  5. #5
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,564
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •