When to use Relationships

[David M C]

I have a database set up to handle various administration tasks. Many of
these systems require employees to be selected. For instance, on a purchase
order, the employee who is requesting the goods is input. At the moment I
have a one-to-many relationship between Employees and Orders. However, is
this right?

In the real world, yes, the employee can have many orders, but from a
systems point of view, the two are unrelated. Who is placing the order is
just another piece of information, just like the unique order number, the
delivery address etc. The only relationship is between the OrderDetails and
the Order.

Basically, I'm asking when you should and shouldn't use a relationship, even
though one could argue there is a relationship there.

Dave

 

[Jerry Whittle]

Yes you should have a relationship between those to tables. If the Orders
table requires an employee entry, you should also have referiential integrity
enabled. That way you don't get orphans in your Orders table where it looks
like no one, or some bogus employee, took the order.

Some day management will want to know something like the number of orders
per employee in the last year. Having that relationship defined will make the
query run quicker.

 

[Tim Ferguson]

At the moment I
have a one-to-many relationship between Employees and Orders. However,
is this right?

Relationships between entities are a basic component of the Data Model.
Just as you decide that every Order has to have a DateOrdered, a
BillingAddress, a DeliveryAddress, a SalesPerson, and an InvoiceNumber;
yo then go on to map the addresses to the Addresses table, the salesman
to the Employees table, and so on.

If you don't care who the salesperson was, then don't store the
SalesPerson attribute and don't have a relationship. If you want to store
the SalesPerson but don't care whether it's a real person or not, then
don't have the relationship -- but in that case it's up to you to decide
what do with all the Orders made by 102276 when there is no such person
with that ID in the company.

Relationships do two, very simple, things only.

1: They prevent an entry into a field (the FK field) which does not match
a record in the referenced table -- in this case, the 102276 could never
have found its way into the Orders.Salesperson fields.

2: They prevent deleting a record in the referenced table if there are
still FK values pointing at it. In other words, if person 102276 used to
exist, it would not have been possible to delete the record while there
were still Orders referring to him.

As such, they are about the most powerful method of preventing your
database disintegrating into a pile of self-contradictory nonsense.
Database design is all about accuracy and integrity.

Hope that helps


Tim F

 

[Tim Ferguson]

an obvious third thing:

3: They can automatically update/delete referencing rows when the
reference row is updated/deleted [CASCADE action].

Of course there is still the ON DELETE SET NULL action but I believe
your first point covers that one.

Not sure that it is an obvious thing... I can't remember ever having used
ON CASADE, and ON DELETE SET NULL is only a special case of cascade.

I worry about cascading because (a) it suggests that you are not
confident of the robustness of your PKs and (b) it can allow massive
record deletion in silence and with no 'undo' facility.

If my users want to get rid of stuff, they end up with a dialog that says
"you are about to delete 457 visits records -- are you sure?" and then I
create the separate DELETE commands on each table affected iff the user
agrees. Similarly, if someone wants to change a PK value, then she can
site down and create the new record by hand and then delete the old
one... Seriously, I do know that other people find it an important tool
and I am not for a moment criticising. My point is just that I have not
yet come across a project in my own work where there isn't a better
alternative.

All the best


Tim F

 

[Tim Ferguson]

By you not using CASCADE (SQL commands in uppercase, not shouting <g>)
and instead deleting referencing rows 'by hand' suggests that you are
not confident of the robustness of the engine.

No: it's not the engine I don't trust, it's the user. Heck, even I've
deleted chunks of stuff I didn't mean to, from the file manager as well
as in databases: we've all done it. My job as a designer is to make it ah
hard as possible.

I like it when the engine does what it's told without asking me if I'm
sure <g>. Seriously, what is the problem with this? If a particular
user/group isn't trusted to with DELETE/UPDATE permissions then revoke
them.

I barely even trust them to create records! They have to work through a
search screen, request a new record, acknowledge that if they create a
duplicate I personally will come and bite their legs off; then and only
then do they get an empty record to type into. Or something to that
effect! Deletions are the same in reverse: less finicky (obviously) when
they could quickly re-enter it should they need to, but with maximum
alerts when the data is unrecoverable or expensive. Yes, I know there are
always backups, but it's not always as easy as that...

This all comes down to style, I guess. I don't _think_ that ON CASCADE
and its friends are a part of the R model, and probably count as what
Pascal calls "a proprietary extension".

B Wishes


Tim F

 

[Tim Ferguson]

You should be preventing the creation of duplicates in the database.
You know that! The great thing about implementing data integrity in
the database (i.e. the proper place) is knowing that any user or
application (written by yourself or anyone else) can do what they like
without messing up the data.

I don't disagree with any of that... but only users can know whether they
have two people called "Luke Hennessy" or it's the same person cropping
up twice: it's not something the db engine can have a vote on.

that Pascal has no interested in SQL, period. I think a concept such
as CASCADE is too implementation related (Tutorial D, whatever) for
the theorists, anyhow.

That's a bit rough. I have a soft spot for Fabian Pascal... he used to
scare the heck out of me in the old compuserve days. Anyhoo -- what's
wrong with theorists??<g>

All the best


Tim F

 

[Immanuel Sibero]

Anyhoo -- what's
wrong with theorists??<g>

Few of them are practical?


Immanuel Sibero

 

[Tim Ferguson]

Few of them are practical?

... and without them, there would be no practice.


Tim F