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.
8  1310 
"VisionSet" <sp**@ntlworld.com> wrote in message
news:Au*****************@newsfep4-winn.server.ntli.net... 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
What, exactly, is inelegant about union? I marvel that you find union
inelegant yet you apparently find NULL markers and outer joins elegant.
"VisionSet" <sp**@ntlworld.com> wrote in message
news:Au*****************@newsfep4-winn.server.ntli.net... 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
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" <bb*****@golden.net> wrote in message
news:XR*********************@mantis.golden.net... "VisionSet" <sp**@ntlworld.com> wrote in message
news:Au*****************@newsfep4-winn.server.ntli.net... 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
What, exactly, is inelegant about union? I marvel that you find union
inelegant yet you apparently find NULL markers and outer joins elegant.
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
"Bob Badour" <bb*****@golden.net> wrote in message
news:XR*********************@mantis.golden.net... "VisionSet" <sp**@ntlworld.com> wrote in message
news:Au*****************@newsfep4-winn.server.ntli.net... 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
What, exactly, is inelegant about union? I marvel that you find union
inelegant yet you apparently find NULL markers and outer joins elegant.
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 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)
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.
> 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)
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.
"Bob Badour" <bb*****@golden.net> wrote in message news:<XR*********************@mantis.golden.net>.. . "VisionSet" <sp**@ntlworld.com> wrote in message
news:Au*****************@newsfep4-winn.server.ntli.net... 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
What, exactly, is inelegant about union? I marvel that you find union
inelegant yet you apparently find NULL markers and outer joins elegant.
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
"Bob Badour" <bb*****@golden.net> wrote in message news:<XR*********************@mantis.golden.net>.. . "VisionSet" <sp**@ntlworld.com> wrote in message
news:Au*****************@newsfep4-winn.server.ntli.net... 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
What, exactly, is inelegant about union? I marvel that you find union
inelegant yet you apparently find NULL markers and outer joins elegant.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Mariusz Jedrzejewski |
last post by:
Hi,
I'll be very grateful if somebody can explain me why my Opera 7.23
(runing under linux) doesn't show me inner tables. Using below code I
can see only "inner table 1". There is no problem with...
|
by: Random Person |
last post by:
Does anyone know how to use VBA to relink tables between two MS Access
databases? We have two databases, one with VBA code and the other with
data tables. The tables are referenced by linked...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my...
|
by: Jill Elaine |
last post by:
I am building an Access 2002 frontend with linked tables to an
encrypted Paradox 7 database. When I first create these linked tables,
I'm asked for the password to the encrypted Paradox database,...
|
by: Shelby |
last post by:
Problem: My company generates its own data export from a propietary
database. These (free) tables can be read in C#.NET using a Visual FoxPro
driver (vfpoledb). I can read each of the six tables...
|
by: phil-news-nospam |
last post by:
In followups by Brian O'Connor (ironcorona) to other posts, he repeats
the idea that using tables in CSS is not something that should be done
because IE doesn't support it. Of course I'm not happy...
|
by: rdemyan via AccessMonster.com |
last post by:
I have a need to add another field to all of my tables (over 150). Not data,
but an actual field.
Can I code this somehow. So the code presumabley would loop through all the
tables, open each...
|
by: db2admin |
last post by:
Hello,
I want to plan rearranging tables in our database according to
business areas. say all tables in business area A will be in seperate
tablespace or tablespaces. I am planning to monitor...
|
by: bubbles |
last post by:
Using Access 2003 front-end, with SQL Server 2005 backend.
I need to make the front-end application automatically refresh the
linked
SQL Server tables.
New tables will be added dynamically in...
|
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |
