Consider these 2 related tables:

Books(ID, Title, Author)
Authors(ID, Name, DOB)

Now another entity 'Reviews' is being built up steadily over time, but the
Reviews are of either Books OR Authors.

How is this entity implemented in the relational database with one table:

Reviews(ID, Author, Book, Comments)

where one of the foreign keys Author or Book is always null, (breaking some
normal form or other?)

OR
two tables:

Reviews_Books(ID, Book, Comments)
Reviews_Authors(ID, Author, Comments)

TIA
--
Mike W

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote


Why do you think that breaks a normal form?

choice B

What happens in choice B when a reviewer writes a combined review? Do
you create a third table or are you going to duplicate data in both
review tables?

I'd go with choice A. Nothing says that parts of a primary key cannot
be null. With A you can create views that are equivalent to the two
tables in choice B. Trying to create a view equivalent to choice A
REVIEW table from the two tables in choice B seems contrived:
select ID,NULL,Book, Comments from Reviews_Books
UNION
select IN, Author, NULL, Comments from Reviews_Authors ;

Personally, I'd use choice A.

"Ed prochak" <ed.prochak (AT) magicinterface (DOT) com> wrote

Okay so reading a book it doesn't break any normal form, but I thought the
general rule of thumb was null values were only advisable if data could
logically fill that field value.

--
Mike W

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote

You were right the first time. NULL breaks 1NF and violates the information
principle.

If there's only ever one review per book and one review per author, then put
the review in the Books or Authors table as a review field.

Otherwise it's got to be two review tables.

If you want to combine the reviews, then why not also combine the Books and
Authors table:

BooksAndAuthors(ID int, IsThisABook boolean, Comments varchar(255) ,
.......)

In fact, why not put everything in one table.

"programmer" <int.consultNOCAPITALS (AT) macmail (DOT) com> wrote


This seemed a simple question but there is not much agreement here!

I think the main reason Reviews can't be a field in Books or Authors is that
Books and Authors (This is just a ) are tables that are more complete, or in
otherwords Reviews may never occur for many books (A 1 to 0 or 1
relationship) So review would be littered with nulls. I think splitting
into two tables here is the usual thing to do.


So 2 review tables or 1? and if 2 do I have 2 columns for the foreign key or
1 and some method of discrimating what key the field value represents?

--
Mike W

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote

Following on from this, with the query requirements
If I assume one table in some form is the way to go with Reviews
This query

select comments, author_id, book_id from reviews
left join books on books.id = reviews.book_id
left join authors on authors.id = reviews.author_id

is very fast but when a book is return author_id is null as expected.
It would be nice to have the relevent author returned for a book review row.

So:

select comments, author_id, book_id from reviews
left join books on books.id = reviews.book_id
left join authors on authors.id = reviews.author_id or reviews.author_id =
books.author_id

will do this admirably - however it bumps up the query time BIG!
because of that OR in the join condition it has to examine every row in the
Authors table.

Any way round this?

TIA
--
Mike W

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote

1NF states that data are represented as values in relations. NULL is not a
value.

Nope. If you use the PRIMARY KEY constraint, **all** columns must be
NOT NULL. Trust me; I did spend ten years on the Standards Committee.
Now, a UNIQUE () constraint can have NULLs. The part I don't
understand is why we never required a PRIMARY KEY constraint on all
tables.

joe.celko (AT) northface (DOT) edu (--CELKO--) wrote in message n
Because, on occasion, a DBMS needs to be able to swallow a file
that looks like this:

--BEGIN--
1
1
1
2
1
1
2
1
3
....etc

As a practical tool for turning this kind of data into something more
normalized, data, SQL is peerless.

SELECT D.Dat, COUNT(*)
FROM Load_Data
GROUP BY D.Dat;

If you were to require every 'table' to have a primary key, then
you would need another kind of schema object to hold this kind
of data.

You might, on the other hand, introduce into the standard the
notion of thing called a 'relation', which has set rather than
bag properties. This wouldn't break anything backward compatability
wise, would simplify the implementation, etc, etc.

CREATE RELATION Foo ( ... );