Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    18

    Unanswered: 3 Table Relationship Questions???

    I have a basic database application set up and I have questions about a 3 table relationship. I have two tables which contain the "one" side of the "One to Many" relationship. I want to have a single child table, the "Many"side, with both "One" sides related to it. I use preset autonumber ranges as my primary keys in both parent tabels, to elminate dups in the child/many side.

    When i try to enforce refrential intergrity it gives me an error stating that a record must exisit in the table that is not being edited. Is there a way around this.. Or... should i have two fields in the child tabel, one for each link? If i did this I would have lots of null values in each linked cloumn??Is this OK??? Is there a better way??

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a stupid example:

    tblFirstName:
    IDfn, auto, PK
    strFn, text
    1; Fred
    2; Sam
    3; Sue
    4; Jenny
    5; Bob


    tblLastName:
    IDln, auto, PK
    strLn, text
    1; Smith
    2; Brown
    3; Baker


    tblName:
    IDn, auto, PK
    IDfn, long, FK on tblFirstName
    IDln, long, FK on tblLastName
    1; 1; 2 = Fred Brown
    2; 1; 3 = Fred Baker
    3; 3; 1 = Sue Smith
    4; 4; 2 = Jenny Brown
    BUT the record...
    5; 2; NULL = ???? Sam NULL ????
    it's up to you how you handle the case of record 5

    it does not make relational sense (to me!!) to design:
    tblBad:
    IDbad, auto, PK
    strFullname, text
    ....and then attempt to fill strFullName from tblFirstName tblLastName

    so YES: two fields

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Quote Originally Posted by Cbauer
    When i try to enforce refrential intergrity it gives me an error stating that a record must exisit in the table that is not being edited. Is there a way around this.. Or... should i have two fields in the child tabel, one for each link?
    if ur getting a ref integrity error it means that existing data doesn't mesh. you have data in the child that is not in the parent, or, as you say, you have nulls in the linked fields in the child.

    you do need a link from both tables.

    Quote Originally Posted by Cbauer
    If i did this I would have lots of null values in each linked cloumn??Is this OK??? Is there a better way??
    it is not OK. you have to fix it first.
    - w

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Do you have a UNIQUE constraint on the text fields of both the First and Last name tables. In otherwords, a natural key in this case. If no, then what is the advantage of having:

    tblFirstname:
    ------------

    1, John
    2, John
    3, John
    ...

    in this database. It would appear to me, that the text string instance 'John' is not in itself a Strong entity, rather an attribute of the corresponding owner. Possible, an instance of Person?

    If, however you do have the UNIQUE constraint then I would advise agains't it, unless you lock down the table, and ensure that no rows can be updated. Why? Because the semantic meaning of 'John' is not unique across the system, in otherwords the entity is not strong, or a true reference table, as these such tables are often called.

    An update of such a record, is infact equal to (logically):

    Delete record / Insert record.

    Because however, you are creating the Foreign Key relationship based on the surrogate, as opposed to Natural key, any logical Delete / Insert of a new First / Last name will be not be prevented.

    Something to consider.
    Last edited by r123456; 03-10-05 at 21:57.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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