Why use database relationships?

  • Thread starter Thread starter R S via AccessMonster.com
  • Start date Start date
R

R S via AccessMonster.com

Why do two or more tables need to be linked by one of the following
relationships: one to one, one to many, many to many.

When, a simple SQL statement UNION ALL (which is what I use) can join two
tables and provide the user with all data between the two tables. A second
SQL statement is used to reduce the joined data to what data is needed. If I
specify a relationship between two tables then I need to be concerned with
what data will be returned by the type of join I use.

Just curious, thanks.
 
R S via AccessMonster.com said:
Why do two or more tables need to be linked by one of the following
relationships: one to one, one to many, many to many.

When, a simple SQL statement UNION ALL (which is what I use) can join two
tables and provide the user with all data between the two tables. A second
SQL statement is used to reduce the joined data to what data is needed. If I
specify a relationship between two tables then I need to be concerned with
what data will be returned by the type of join I use.

Just curious, thanks.
Click to expand...

A UNION can only operate on pairs of tables that have the same number of
fields, in the same order, and with the same datatypes. If you have pairs
of tables such as these in your databases, then I'm afraid that your
database design has gone badly awry.

This is not to say that UNION does not have it's uses - it does - but it's
usefulness is limited.

In a properly designed database, there will be lots of tables in a
one-to-many relationship. In fact, it's the only type of relationship worth
having: one-to-one and many-to-many usually suggests a poor design. The
simplest example is a customers table and an orders table. One customer can
have many orders, hence one-to-many. The customers table and the orders
table have completely different fields, so a UNION is of no use in returning
data from both tables - a JOIN is called for.

A UNION does NOT join tables. In basic set theory terms, a UNION performs a
union between two sets (tables), whereas a JOIN performs an intersection -
totally different operations.
 
The most typical relationship is one-to-many. Perhaps the biggest reason to
have table relationships is the accompanying referential integrity. Rather
than relying entirely on your code to do so, you can have the relationship
structures disallow a customer from being be deleted from the Customers table
if that customer has invoices in the Invoices table. Imagine deleting a
customer that has invoices. Now you have invoices for customer # 239, but no
idea who customer # 239 was.

There are two more features associated with referential integrity that can
be helpful. Cascade Update: if you change the customer ID for a particular
customer, the ID recorded in the Invoices table for each of that customer's
invoices will change to match.

Cascade Delete: automatically deletes all the invoices for the customer when
the customer is deleted (scary, but useful at times). This way, you don't
just get an error telling you that you can't delete the customer, but the
system actually does the cleanup for you.

Another reason is that if you set up the relationship between the tables, it
(the join) appears by default when using the query builder, although you can
break it for a particular query if you like. This can save you a little time
searching for related fields when building your queries.
 
I appreciate the feedback everyone I'll try and adjust my database from
UNIONS to JOINS. Thanks.
 
One more advantage of Joins: Union queries must be created pretty much
manually in SQL, while the query builder has a pretty good arsenal of
semi-graphical tools for adding tables, manipulating joins & join types,
setting criteria, etc. It still creates the SQL, but it can save some time
and give you better visualization of the process.
 
Back
Top