Re: A calculated field and a Master/Detail relationship

From: AlanGLLoyd (alanglloyd_at_aol.com)
Date: 12/29/03 

Date: 29 Dec 2003 08:52:05 GMT

In article <9byHb.13458$VW.639124@news3.tin.it>, "Frank" <iuesei@virgilio.it>
writes:

>Is there a reason why the Master/Detail relationship doesn't work in this
>case or did I made something wrong which I don't realize ?
>

Apart from other suggestions there is an issue with the way you have
implemented the relationship between books and authors which may affect you.

While authors have many books, some books have many authors. The traditional
way to implement this "many-to-many" relationship is to have an AuthorBook
table which contains records relating individual authors to individual books by
storing the BookId and the AuthorId as appropriate. Then this table is related
in a one-to-many relationship to both the Authors and the Books tables using
the appropriate field of each table (BookId to Books and AuthorId to Authors).

OTOH you may never record books which have many authors, or the accuracy of
your database may be fulfilled by using only the first author.

Or as Bruce has suggested, use a query.

Alan Lloyd
alanglloyd@aol.com

Relevant Pages

  • Re: Consolidate duplicate entries
    ... BookID and AuthorID if you want to record the ... Am rebuilding a book db which lists approx 40k of books, ... AuthSName and AuthFName are text fields while BookID and AuthorID are ...
    (microsoft.public.access.tablesdbdesign)
  • Re: compond index and key faster/better?
    ... > bookid int identity ... > alter table books add constraint PK_books primary key clustered ... Since bookid is unique, why add authorid ... Having the clustered index on authorid is probably better than clustering ...
    (comp.databases.ms-sqlserver)
  • Re: A calculated field and a Master/Detail relationship
    ... I don't know if I understood the TQuery suggestion. ... > While authors have many books, ... > storing the BookId and the AuthorId as appropriate. ...
    (comp.lang.pascal.delphi.misc)
  • Re: Relational Database Question
    ... as it allows for the possibility of books being written by ... DBMS's architecture itself, below the level at which your schema resides. ... Date's _Introduction to Database Systems_. ... > AuthorID ...
    (microsoft.public.sqlserver.programming)
  • Re: compond index and key faster/better?
    ... Since bookid is unique, why add authorid ... Only for the purpose of having the clustered index that way. ... > Then again, PK of a books table should probably be the ISBN, as that ...
    (comp.databases.ms-sqlserver)