Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Multiple foreign keys?

    I have four tables: A, B, C, Z.

    Each of the records in A, B, C has a single related record in Z. Thus, tables Am B and C each includes a foreign key to a record in Z.

    My question is: How to I include foreign keys in the records in Z back to the related record in A, B and C? Or is there a better way to design this kind of relationship?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sleepless
    My question is: How to I include foreign keys in the records in Z back to the related record in A, B and C?
    my question is why?

    Quote Originally Posted by Sleepless
    Or is there a better way to design this kind of relationship?
    what kind of relationship?

    you haven't given us anything to go on other than a few letters that you say represent tables that have some kind of undefined relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Quote Originally Posted by r937
    my question is why?

    what kind of relationship?

    you haven't given us anything to go on other than a few letters that you say represent tables that have some kind of undefined relationships

    Each record in A belongs to a record in Z.
    Each record in B belongs to a record in Z.
    Each record in C belongs to a record in Z.

    Records in Z belong to either A or B or C.

    Does this help?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sleepless
    Does this help?
    no, it doesn't

    if it were as simple as you say, just create the foreign keys and you're done

    obviously it's not that simple, but if you're going to hide the real information behind fake names like A and B and C, i can't help you any further
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by Sleepless
    I have four tables: A, B, C, Z.

    Each of the records in A, B, C has a single related record in Z. Thus, tables Am B and C each includes a foreign key to a record in Z.

    My question is: How to I include foreign keys in the records in Z back to the related record in A, B and C? Or is there a better way to design this kind of relationship?
    How about using three foreign keys. In SQL:

    ALTER TABLE A ADD CONSTRAINT afk1 FOREIGN KEY (col) REFERENCES z (col);
    ALTER TABLE B ADD CONSTRAINT bfk1 FOREIGN KEY (col) REFERENCES z (col);
    ALTER TABLE C ADD CONSTRAINT cfk1 FOREIGN KEY (col) REFERENCES z (col);

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Sleepless
    Each record in A belongs to a record in Z.
    Each record in B belongs to a record in Z.
    Each record in C belongs to a record in Z.

    Records in Z belong to either A or B or C.

    Does this help?
    One way to enforce this is to include a column in table Z that indicates the type of record. This column will also be included in tables A, B, and C, but the values in each of these tables will be constant. Then, you set up a composite foreign key including both the type and the primary key for Z in each of the other tables.
    This ensures that each Z record be mapped to only one record in any of the other three tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    In case it helps, here's an example of what Blindman described:

    Data Based : Distributed Keys and Disjoint Subtypes

  8. #8
    Join Date
    Aug 2009
    Posts
    68
    Arent't you trying to map an object class hierarchy to the database ?
    (i.e. in your application are A,B,C objects and inherit from Z ?)

    Regards,

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by dportas
    In case it helps, here's an example of what Blindman described:

    Data Based : Distributed Keys and Disjoint Subtypes
    Exactly. Nice link. My explanation was rather rushed this morning.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Aug 2009
    Posts
    68
    In that case, I use common primary keys.
    Suppose Z is the main table and A,B,C are subtypes of Z. AA is subtype of A and S a sequence. When creating a row in table A, I compute a primary key concatening 'A-' to the next value of S. Then when creating the related row in table Z, I set the primary key to the same value.

    In table AA, the computed key would be 'AA-nextvalue(S)' and would be stored in primary key columns of table A and table Z.
    Of course, table names can be replaced with shorter strings, provided one can associate these prefixes to the tables.

    So executing a query on table Z, the prefix of the primary key 'AA-12546' tells me what kind of type it is, (table AA) and the key to retrieve the full thing (which is the OP problem, I think):
    Select * from Z,A,AA where Z.ID = A.ID and A.ID = AA.ID and AA.ID = 'AA-12546';

    Hope this helps,

  11. #11
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Sleepless

    Yeah, I think the problem is simple and the answer in Relational Database terms is simple. The issue is obstructed by the fact that your starting point is, you have surrogate keys instead of relational keys in all tables: that is a created problem (it did not exist in the relational model), which you were then seeking a solution for. The best advice is, forget about the spreadsheet model; implement the relational model, and you simply do not have the problem.

    Second, the avoidance of standard relational terms is a hindrance to you. If
    Each record in A belongs to a record in Z.
    Each record in B belongs to a record in Z.
    Each record in C belongs to a record in Z.
    is true, then
    Records in Z belong to either A or B or C.
    cannot be true.

    Relations are identified "one-way" only; the reverse must be expressed differently, it cannot be the same. If the first three statements identifying the nature of each relation (these are called Verb Phrases) are true, then there will be a statement or Verb Phrase for the same three relations, expressed in reverse:
    Z is a parent of A
    Z is a parent of B
    Z is a parent of C
    These Verb Phrases actually assist you, the modeller, is resolving the model. What I am saying is, don't avoid the rules, they support each other; they are all there to help you.

    Back to the original question:
    My question is: How to I include foreign keys in the records in Z back to the related record in A, B and C? Or is there a better way to design this kind of relationship?
    The latter. The parent-child relation already exists, and can easily be determined in either direction without adding anything. (If you need a separate discrete child-parent relation, then it is a diffferent problem: your model is not resolved yet.) We need a concrete example. Here's an example posted in a recent thread, that we can use here; it has the relatinal keys migrated properly (which I believe is central to understanding the problem); it has both straight 1:n parent-child and subtype 1:1 parent-child. (You can post back, confirm, deny)
    Z = Answer
    A, B, C = AnswerEmployment, AnswerGender, AnswerFirearm
    The subtypes here are exclusive, they can be inclusive, no problem.
    Assuming of course:
    1 We have a relational database with declarative referential Integrity; and foreign keys as per dportas' post. For a child, the existence of the parent is guaranteed, it does not have to be "checked".
    2 We are using natural, relational keys, which means compound keys due to migration; not surrogate keys

    For a parent Answer, the existence of a child AnswerGender is determined by a simple join:
    Code:
    SELECT 1 
        FROM  Answer A,
              AnswerGender AG
        WHERE A.PersonId = AG.PersonId
        AND   A.QuestionId = AG.QuestionId
        AND   A.Sequence = AG.Sequence
    Unless you cripple the database by avoiding relational keys and migration of same, you can join any parent to any grandchild (or great-grandchild, etc) directly (without being forced to access intermediate tables):
    Code:
    SELECT 1 
        FROM  Question Q,
              AnswerGender AG
        WHERE Q.QuestionId = AG.QuestionId
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Derek Asirvadem
    If
    Each record in A belongs to a record in Z.
    Each record in B belongs to a record in Z.
    Each record in C belongs to a record in Z.
    is true, then
    Records in Z belong to either A or B or C.
    cannot be true.
    Logically incorrect. Think about it, Derek.....

    Quote Originally Posted by Derek Asirvadem
    Relations are identified "one-way" only; the reverse must be expressed differently, it cannot be the same.
    Demonstrably incorrect. I have a sister. My sister has a brother. If only one of us "belongs" to the other, then who has priority?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by blindman
    Logically incorrect. Think about it, Derek.....


    Demonstrably incorrect. I have a sister. My sister has a brother. If only one of us "belongs" to the other, then who has priority?
    For the bathroom, she does, for the remote, depends on what's on.

    Anyhow, I'm with Rudy on this one. The OP doesn't know his terminology well enough to explain his problem without explaining what the tables are, and he won't post what the actual tables are, so we've got no question.

  14. #14
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Logically incorrect.
    No. I've supplied my statement complete with logic. Since it is being denied, the attempt at refutation will have to supply its "logic". Until then it is an empty statement devoid of logic and reason.
    Demonstrably incorrect. I have a sister. My sister has a brother. If only one of us "belongs" to the other,
    Logically, by definition, since they are siblings, neither "belongs" to the other; both siblings belong to a common parent. Additionally, that has nothing to do with OP.
    Separately, if there are incestuous relations, or if there is a formal relation between the siblings (other than via the common parent), then that should be supported via a separate formal relation
    who has priority ?
    Again nothing to do with OP, the "demonstration" is irrelevant to the issue; the rule against which correctness or lack of it, is unidentified.
    Separately, if priority (for the bathroom, or the single bed, or whatever) is required, and not immediately identified by the keys at hand, then a separate method that identifies the priority over whatever object priority is allocated, can easily be implemented.
    If clarification is required, I do not have my private definitions of Normalisation or the RM; I am quite happy with Codd and Date; and IDEF1X (required for many years, for DoD and govt work) in addition. A relation exists both-ways with migrated keys (and the other "side" can by ascertained from the single relation, without additional elements, that is the central point to OP); but it is expressed one-way.

    Quote Originally Posted by pootle flump
    ... please can supplementary questions from people other than the OP go in a separate thread?
    If you have questions that are unrelated to the OP, such as these, please open a separate thread.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Derek Asirvadem
    If
    Each record in A belongs to a record in Z.
    Each record in B belongs to a record in Z.
    Each record in C belongs to a record in Z.
    is true, then
    Records in Z belong to either A or B or C.
    cannot be true.
    OK Derek, I will dumb it down for you, but then you have only yourself to blame for looking stupid:

    Code:
    Table A
    Key	ZKey	Description
    1	1	Sample record in A
    
    
    Table B
    Key	ZKey	Description
    1	2	Sample record in B
    
    
    Table C
    Key	ZKey	Description
    1	3	Sample record in C
    
    
    Table Z
    Key	Description
    1	Belongs to Sample record in A
    2	Belongs to Sample record in B
    3	Belongs to Sample record in C
    Each record in A, B, and C belongs to one record in Z. Each record in Z belongs to one record in either A, B, or C.
    Exactly what you have just claimed is logically impossible.

    Now, need I continue to explain how ignorant your other assertions were? Or could you figure that out yourself if you put a little thought into 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
  •