oracle DB design question-clarifications....

From: <ziv103_at_my-dejanews.com>
Date: Fri, 14 Aug 1998 15:14:03 GMT
Message-ID: <6r1k7r$hpk$1@nnrp1.dejanews.com>

hello,
To calrify my original question (see below slashed line).  A "book" is assumed to be written
by one author only. If my design for books_table will be #1 than I would have a unique_constraint on the book_code column. The only reason to include author_code in the PK of this table is to allow a FK to be defined on the readers_books table (mentioned later on) to the books_table. The same assumptions and thus design considerations apply for the reader_table (likes to read only one author).

The logic behind the DB is that a reader may only read books written by his/hers favourite author and I would like to verify that when inserting a row to the readers_books table. Having that, would my design for the DB be:
(1) author table: (*)author_code, author_name, ......

    books table: (*)book_code (*)author_code, book_name,.....     readers table: (*) reader_code (*)author_code reader_name,.......     readers_books table: (*)reader_code (*)book_code (*)author_code

    This solutions allows FK to be defined to tables (2)(3) (thus verifying     the logic requirements using FK) but it seems author_code is redundant     in the readers_books context.

(2) author table: (*)author_code, author_name, ......

    books table: (*)book_code ,book_name,author_code(FK),.....     readers table: (*) reader_code, reader_name, author_code(FK).......     readers_books table: (*)reader_code (*)book_code

    This solution has reduced PK size in all tables and reppresents the     entities relations more accurately. To verify the logic     I would need a database trigger on readers_books table (before insert)     that would check, using book and aothor code (vs. the matching tables),     a match betwwen author code (selected from books and readers table).

Which design would you choose from the above 2 options taking into consideration the method of verification (FK or triggers) of the logic and as important - why?

Thanks again for your help.

---

In article <6qvhdf$pq1$1_at_nnrp1.dejanews.com>,   ziv103_at_my-dejanews.com wrote:
> hello,
> If I have an entity (for instance a "book") which is ALWAYS related
> to another entity (for instance an "author"). I have one decoding table
> for "authors" (numeric sequential code + description). Which way is
> preferred when designing the "books" table:
> 1. *author_code *book_code au_name ... (2 primary keys)
> 2. *book_code bk_name author_code ... (1 primary key and FK+index
> on author_code)
> I am asking because suppose there is a third table containing
> information on the entity "reader" whereas each reader likes to read
> books by a specific author. readers table would look like this:
> (*reader_code reader_name author_code(FK) age....)
> If i have a table containg the books each
> reader has read, where i want to verify through it, the book is by the
> author he likes. Should this table have:
> 1. three PK for solution (1) thus verifying integrity with a FK.
> (*author_code *book_code *reader_code)
> 2. two PK for solution (2) thus verifying integrity with say a trigger
> (*reader_code *book_code)
>
> Thanks for any help
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Aug 14 1998 - 10:14:03 CDT