relationships and reference tables

S

SUZYQ

I have a users table that has one record for each user in the system.
The primary key is the UserID. The UserID field shows up in many other
main tables (such as who logged in the entry in the Mail table, or who
QC'd the work).

In the relationships window, should I try to set up a relationship with
the users table and every other table where the userID appears?

Of course, if I add the users table again it will appear as users_1.
Is this OK?
 
D

David Lloyd

Relationships are useful if you want to enforce referential integrity, as
well as implementing cascading updates and deletes.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a users table that has one record for each user in the system.
The primary key is the UserID. The UserID field shows up in many other
main tables (such as who logged in the entry in the Mail table, or who
QC'd the work).

In the relationships window, should I try to set up a relationship with
the users table and every other table where the userID appears?

Of course, if I add the users table again it will appear as users_1.
Is this OK?
 
A

Albert D.Kallal

I have a users table that has one record for each user in the system.
The primary key is the UserID. The UserID field shows up in many other
main tables (such as who logged in the entry in the Mail table, or who
QC'd the work).

In the relationships window, should I try to set up a relationship with
the users table and every other table where the userID appears?

You certainly can do this. Since you are not using referential integrity
(cascade deletes, cascade updates, and enforcing the relationships). In
effect, you kind don't have a relationship, but as you mention just a table
of values used for lookups. ( I mean, you are free to add users to the users
table..but no other tables need to be updated).
Of course, if I add the users table again it will appear as users_1.
Is this OK?

Well, it is ok, but you are drawing the relationships the wrong way. If you
have 10 tables that use the userID, then you would draw the lines FROM each
of those tables to the user table, NOT the other way around. So, while
dropping in a table more then once into the relationships window is ok, your
example case does not apply, nor is it required to drop in the tables more
then once (you are drawing the relationship the wrong way). Further, since
those tables may, or may not yet have the userID set, the you should be
drawing a LEFT join to show this fact.

Now, it turns out that you CAN consider enforcing relationships. For
example,
if a user no longer works at the company, and you delete all of his records
in the database, the next step would be to delete the user in the user
table. If you enforce RI, you will NOT be able to delete the user until all
tables with records that have that userID are deleted. I mean, how
will you know you can safely delete a user who is gone?
So, even for lookup tables, enforcing RI
can be useful. I will admit that I often do not enforce RI for
these lookup tables, and I don't think it is such a bad thing when you
don't.

However, the question still remains, should you draw in those relationships
when in fact no RI is enforced? After all, they those lines don't change ANY
functionally in the database..so why bother?

Well, actually, yes, you should bother and draw the lines for documentation
purposes.

Here is a repeat post of mine on this issue. I first explain the left join
issue, and then "why" draw the lines...

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and YOU writing code, you will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

So, that ER diagram is a fabulous tool, and is kind like a archeological
"dig" that shows you much of what the original developers assumptions were.
It is by far and away the BEST documentation tool you have.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top