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(i d, 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_i d
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_i d 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.com ments from book_review
join book on book_review.boo k_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.c omments from author_review
join author on author.id = author_review.a uthor_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  1324 
"VisionSet" <sp**@ntlworld. com> wrote in message
news:Au******** *********@newsf ep4-winn.server.ntl i.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(i d, 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_i d
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_i d 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.com ments from book_review
join book on book_review.boo k_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.c omments from author_review
join author on author.id = author_review.a uthor_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******** *********@newsf ep4-winn.server.ntl i.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(i d, 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_i d
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_i d 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.com ments from book_review
join book on book_review.boo k_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.c omments from author_review
join author on author.id = author_review.a uthor_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******** *************@m antis.golden.ne t... "VisionSet" <sp**@ntlworld. com> wrote in message
news:Au******** *********@newsf ep4-winn.server.ntl i.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(i d, 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_i d
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_i d 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.com ments from book_review
join book on book_review.boo k_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.c omments from author_review
join author on author.id = author_review.a uthor_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******** *************@m antis.golden.ne t... "VisionSet" <sp**@ntlworld. com> wrote in message
news:Au******** *********@newsf ep4-winn.server.ntl i.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(i d, 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_i d
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_i d 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.com ments from book_review
join book on book_review.boo k_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.c omments from author_review
join author on author.id = author_review.a uthor_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.n et>... "VisionSet" <sp**@ntlworld. com> wrote in message
news:Au******** *********@newsf ep4-winn.server.ntl i.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(i d, 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_i d
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_i d 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.com ments from book_review
join book on book_review.boo k_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.c omments from author_review
join author on author.id = author_review.a uthor_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.n et>... "VisionSet" <sp**@ntlworld. com> wrote in message
news:Au******** *********@newsf ep4-winn.server.ntl i.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(i d, 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_i d
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_i d 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.com ments from book_review
join book on book_review.boo k_id = book.id
join author on author.id = book.author_id
union
select author.name, null, author_review.c omments from author_review
join author on author.id = author_review.a uthor_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 other browsers
(I checked it under Konqueror).
Thank you in advance for your help.
Regards.
/Mariusz
<HTML>
|
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 tables in the database
where the code resides. If we move the database with the data tables to
a new directory, the links are no longer valid.
I tried to update the links by changing the Connect property and refreshing:
Set td = db.TableDefs(0)...
|
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 database,just help in getting me pointed in
the right direction.
I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then...
|
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, and the
linked tables are successfully created. I use the data from these
linked tables in several forms.
All works great until I close the Access frontend and open it again.
When I try to use the forms, I get an error message: "Could not...
|
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 into its own datatable,
modify them, and add them to a dataset. It take approximately 15 minutes to
pass that dataset to Crystal Reports (45 minutes if the report uses three
subreport datasets). Then it takes over 7 hours for Crystal to...
| | |
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 about the fact
that IE doesn't support CSS tables. But what can one do about that?
And tables of one type or the other are needed in some cases (regardless
of whether some people feel it is appropriate or not). So the issue I
and considering...
|
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 table in design mode and then add the new field and set its
properties.
Thanks.
--
|
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 activity on tables
for a month by taking snapshots on tables and then i will see if this
rearrangement is possible.
What are the factors, measures etc. i should consider for rearranging
tables.
|
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 the future, so the front-end
application
must have a way to keep up with this (instead of manually linking
them).
|
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) and the 'employee
name'. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
