Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Unanswered: two instance of same foreign key in a table

    Hi All,

    I have a table bookMaster and authorMaster. The requirement is each book may have three authors. I want to store the foreign key authorId in the booksMaster table. I have created three fields authorId1, authorId2, authorId3 in bookMaster to store the authorID.

    I further try to establish the referential integrity between these three authorId1, authorId2, authorId3 and authorId from authorMaster. MS Access gives me an error saying you cannot do this.

    I urgently need a help for following:

    1.How to achieve this in MS Access so that, any change in authorMaster is cascaded back to the entry in bookMaster.

    2. Is it the right way of doing this or should I need to break my table in three seperate tables like: tableBookAuthor1, tableBookAuthor2, tableBookAuthor3with bookId from bookMaster and authorId from authorMaster

    3. what does normalization rules have to say about such scenario.

    You help will be highly appreciated as the answer to this will have a major change in my code and design.

    Regards,

    Sangram

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Quote Originally Posted by bt_sangram
    I have a table bookMaster and authorMaster. The requirement is each book may have three authors. I want to store the foreign key authorId in the booksMaster table. I have created three fields authorId1, authorId2, authorId3 in bookMaster to store the authorID.
    Eeeek.

    Quote Originally Posted by bt_sangram
    1.How to achieve this in MS Access so that, any change in authorMaster is cascaded back to the entry in bookMaster.
    Foreign key relationships.

    Quote Originally Posted by bt_sangram
    2. Is it the right way of doing this or should I need to break my table in three seperate tables like: tableBookAuthor1, tableBookAuthor2, tableBookAuthor3with bookId from bookMaster and authorId from authorMaster
    No.... and no.

    Quote Originally Posted by bt_sangram
    3. what does normalization rules have to say about such scenario.
    The first rule says "don't do it like that".

    Have a read of this article:
    http://www.r937.com/relational.html

    You should read it all but really pay attention when you get to the bit about many to many relationships and first normal form.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Posts
    5
    HI,

    Was a good article to read.

    Can u also suggest me that if I break my existing table to store the bookId and authorId seperately, should I create three different tables: tableBookAuthor1, tableBookAuthor2, tableBookAuthor3 with bookId from bookMaster and authorId from authorMaster as the sequence of author name is also important to me

    or

    Should I achieve this my creating one single table called bookAuthor where I store bookid, authorId and an additional filed to store the sequence of author(whether he is a first/ second or third author of the book)

    Thanks,

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Not really
    if you have multiple foreign realtionships then Id expect to see one table per interealtionship. So that would be one table to hold all Authors & all Books... cue the bit from Lord of the rings.... "One table to bind them....."

    eg
    book table
    BookID 'PK
    <blah> 'other stuff relating to the book
    ...

    author Table
    AuthorID 'PK
    <blah> 'other stuff relating to the author
    ...

    BooksAuthors Table
    BookID 'identifies what book
    AuthorID 'identifies what author

    Variously its called an intersection table.....

    This design would allow you to have as many authors as required, not just 3.

    If you design requirement is such that you know you will only ever have 3 authors, then I'd be tempted to leave them in the book table as author1,2,3.. but thats based solely on the premise that you will never ever ever exceed 3 authors, a requirement I've only ever heard of in schools and uni coursework.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bt_sangram
    Should I achieve this my creating one single table called bookAuthor where I store bookid, authorId and an additional filed to store the sequence of author(whether he is a first/ second or third author of the book)
    A relational set of data has no intrinsic order so if there is an order to your authors you need to impose it yourself. So yes - an "AuthorOrder" column would be required. Remember that this would now form part of an alternate key and require enforcing (as per the article)

    Quote Originally Posted by healdem
    If you design requirement is such that you know you will only ever have 3 authors, then I'd be tempted to leave them in the book table as author1,2,3.. but thats based solely on the premise that you will never ever ever exceed 3 authors, a requirement I've only ever heard of in schools and uni coursework.
    Have to disagree there Mark. Use the "AuthorOrder" column (integer) that bt_sangram suggested but add a check constraint so that the AuthorOrder can only be 1, 2 or 3. If ever the requirement came for 4+ authors just change the constraint. The schema remains the same, all queries remain the same, all application code.... you get my point?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bt_sangram
    Can u also suggest me that if I break my existing table to store the bookId and authorId seperately, should I create three different tables: tableBookAuthor1, tableBookAuthor2, tableBookAuthor3 with bookId from bookMaster and authorId from authorMaster as the sequence of author name is also important to me
    I guess you missed it in my first post - I don't like this idea much either
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    ...Have to disagree there Mark.....
    personally I wouldn't go down the route of having 3 author slots in the book table. IMHV if there is more than one author, then it needs to be in a intersection table (as soem bright spark will produce a book with god knows how many authors that will always break such a approach. However if the user requirement says there will never ever be more than 3 authors, and its set in concrete then it may well be appropriate to store the author(s) in the book tables, hence my comment of "...a requirement I've only ever heard of in schools and uni coursework.". limiting it to 3 authors sounds very odd to me, but if thats waht the user requirements is, then who are we to say different.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    personally I wouldn't go down the route of having 3 author slots in the book table. IMHV if there is more than one author, then it needs to be in a intersection table (as soem bright spark will produce a book with god knows how many authors that will always break such a approach. However if the user requirement says there will never ever be more than 3 authors, and its set in concrete then it may well be appropriate to store the author(s) in the book tables, hence my comment of "...a requirement I've only ever heard of in schools and uni coursework.". limiting it to 3 authors sounds very odd to me, but if thats waht the user requirements is, then who are we to say different.
    Good point. Sounds more NZDF than BCNF though to me IMHO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by healdem
    cue the bit from Lord of the rings.... "One table to bind them....."
    *groan* oh, no he didn't

    Just to be a jerk and add a layer of complexity. Someone was kind enough to help with with something that had some similarities.
    A concatenated key on BookID and AuthorOrder is a good idea. That way you don't have two Author Number 2s for one book.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by starkmann
    Just to be a jerk and add a layer of complexity. Someone was kind enough to help with with something that had some similarities.
    A concatenated key on BookID and AuthorOrder is a good idea. That way you don't have two Author Number 2s for one book.
    I presume you mean a composite key (i.e. two fields) not concatenated values (authororder concatenated with the bookid in the BookID field)? If so - that is the bit I meant about alternate keys.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, i disagree with having AuthorOrder as part of a key

    the correct implementation is --
    Code:
    CREATE TABLE BookAuthors
    ( BookID      INTEGER NOT NULL 
    , FOREIGN KEY ( BookID ) REFERENCES Books ( ID )
    , AuthorID    INTEGER NOT NULL 
    , FOREIGN KEY ( AuthorID ) REFERENCES Authors ( ID )
    , PRIMARY KEY ( BookID , AuthorID )
    , AuthorOrder SMALLINT NOT NULL DEFAULT 1
    );
    uniqueness on the AuthorOrder within each book is not desired, because not every book has primary/secondary authors, and in those cases, you don't want to have to make them up just to fit some arbitrary constraint
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by pootle flump
    I presume you mean a composite key (i.e. two fields) not concatenated values (authororder concatenated with the bookid in the BookID field)? If so - that is the bit I meant about alternate keys.
    d'oh you are correct. Green tea is still steeping, no caffiene yet.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So you are perfectly happy to allow:
    Code:
    INSERT INTO BookAuthors(BookID, AuthorID, AuthorOrder)
    VALUES (1, 1, 1)
     
    INSERT INTO BookAuthors(BookID, AuthorID, AuthorOrder)
    VALUES (1, 1, 1)
    despite
    Quote Originally Posted by bt_sangram
    ... the sequence of author name is also important to me
    ????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by r937
    actually, i disagree with having AuthorOrder as part of a key
    ....
    uniqueness on the AuthorOrder within each book is not desired, because not every book has primary/secondary authors, and in those cases, you don't want to have to make them up just to fit some arbitrary constraint
    I'm probably missing a key point here but if you only have one author, then you only have one entry so you don't violate the rule, same with two or even if you ended up with four.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by starkmann
    d'oh you are correct. Green tea is still steeping, no caffiene yet.
    Nice - I'm on the Green Tea today too Caffiene defo in effect though. <jitter/>
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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