I have already posted this under 'Simple table organisation question'
here is a more lucid version. It will be under MySQL v4.0 which now supports
unions

Consider 2 entities - books & authors and their respective tables:

book(id, title, author_id)
author(id, name)

Now consider another entity: review. Reviews can be of either books or
authors, so is this one entity or two?

One table:
· review(id, book_id, author_id, comments)
Here for any one row either book_id or author_id will be null.

· review(id, book_or_author_id, discriminator, comments)
Here the foreign key (book_or_author_id) can represent either table and thus
avoids null field values, the 'discriminator' column simply states which
entity the review represents (eg 'A' for author or 'B' for book).

Two tables:
· book_review(id, book_id, comments)
author_review(id, author_id, comments)

Now the queries.
For one table we have

· select * from reviews
left join book on book.id = review.book_id
left join author on author.id = review.author_id

The problem here is that for book reviews the row has a null value for
author, obviously it would be nice to know this.

· select * from reviews
left join book on book.id = review.book_id
left join author on author.id = review.author_id or author.id =
book.author_id

This addition achieves this but with the 'or' in the join condition forces a
full table scan and long query times. May be restructuring the query can
avoid this?

For two tables a 'union' seems unavoidable.

· select author.name, book.title, book_review.comments from book_review
join book on book_review.book_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.comments from author_review
join author on author.id = author_review.author_id

The null in the 2nd select replaces the book.title column for author
reviews.
This is very fast, but unions are a bit inelegant.

So which approach is best, can the query for 1 table be improved or is it
more correct to have 2 tables and do the union? Any other comments you may
have on the approach to this would be welcomed

Cheers,
Mike.

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

What, exactly, is inelegant about union? I marvel that you find union
inelegant yet you apparently find NULL markers and outer joins elegant.

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote

I'm asking for advice, I know little.
I find extracting info on database from the internet desperate.

Do you know of any resources that explain data modeling of real and other
than straightforward situations.

--
Mike W

I would say that reviews about books are different from reviews about
authors, so they need to be in different tables.

If there was a mechanism that would make 'id' unique between the 2 tables
'book' and 'author', then the single 'review' table might be more be
workable.

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

Advice I can give: Go to the library and borrow a good book on data
modelling--heck, borrow a couple of them. I also highly recommend Fabian
Pascal's _Practical Issues in Database Management_ and all of Chris Date's
various "Writings" books.

Data modelling requires full knowledge of the business requirements. Anyone
who gives you specific design advice on the basis of a short email message
is giving you advice on the basis of almost complete ignorance and is acting
irresponsibly.

To answer your question you need to know what the data means to the
business, what the business' specific performance requirements are and
unfortunately due to the weaknesses of most dbmses you need to know the
specific physical capabilities of your dbms.


That's because the internet is the wrong place to look.


See above. Fabian's book in particular does a good job of exploring the more
difficult modelling issues. Taking a quick glance at the table of contents,
I suspect you will find chapters 6 and 10 and perhaps others informative.

"Bob Badour" <bbadour (AT) golden (DOT) net> wrote


And I puzzle over his notion that the single table solution cannot
have both book_id and author_id columns populated on the same row. And
nothing wrong with a UNION either. It's getting to a question of style
at this point if there is not any other firm criteria to guide the
selection.

Ed