DB design question-opinions?

hello,
I have a DB which includes the following tables:
(-) authors_table (*)author_code,author_name,age,..... (this design is given)
(-) books_table
(-) readers_table
(-) readers_books (which books the reader has read)

assumptions:
A "book" is written by one author only. A "reader" has one favourite author only.

I would like to verify that a book read by a reader is written by his/hers favourite author when inserting a row to the readers_books table.
(if there is no match, the insert operation should fail)

Having that, would my design for the DB be:

(1) books table: (*)book_code (*)author_code, book_name,.....

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

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

(2) 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 (that's it)

    This solution has reduced PK size in all tables and represents the     entities relations more accurately. To verify the logic I will have     to define a database trigger on readers_books table (before insert)     that would select author_code from books_table and readers_table,     using book_code and reader_code respectively.     A match between the 2 author codes will be checked in the trigger.

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.

Ziv

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