When to use Relationships

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
At the moment I
have a one-to-many relationship between Employees and Orders. However,
is this right?
Click to expand...

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 said:
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 [can] 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. [NO ACTION]
Click to expand...

Great post but allow me to add 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.

Jamie.

--
 
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.
Click to expand...

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 said:
I can't remember ever having used ON CASADE
Click to expand...

Seriously, wow.
it suggests that you are not confident of the robustness of your PKs
Click to expand...

I don't understand your point. For me, it's the opposite case i.e. I
want to assert that my keys (all my candidate keys - who says DRI is
limited to PKs?) are robust.

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. Actually, I can't
believe that is true: if you can't trust the engine, what can you trust
it can allow massive record deletion in silence and with no 'undo' facility
Click to expand...

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.

And sure you have an undo facility: ROLLBACK TRANSACTION.
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?"
Click to expand...

There's no reason why you can't do this with a CASCADE e.g. you can
query beforehand to give the user feedback on how many records would be
affected, you could even execute the command in a transaction and
present a view of the data before issuing ROLLBACK (undo) or COMMIT
(apply).
then I create the separate DELETE commands on each
table affected iff the user agrees.
Click to expand...

So you must be doing this in a transaction so you can ROLLBACK at the
point when they bail out, yes?

Don't all these messages get annoying e.g. when deleting an employee
you'd get repeated messages ('Are you sure you want to delete this
employee's earnings history?', 'Are you sure you want to delete this
employee's pension contributions history?', etc) when it is implicit
that deleting an employee means you want to remove rows from related
tables e.g. you could show one warning message ('Deleting this employee
will also deleted their earnings history, pension contributions,... Are
you sure?') and likewise execute one delete action.
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.
Click to expand...

Likewise, I am not criticising. I'm just finding it hard to think why
deleting rows from each table individually is 'better'. If the end
result is the same (i.e. all related rows removed/altered) then why is
it better not to let the engine do it automatically?

I don't deny there are cases when cycles could occur and other case
when the engine is not clever enough to deduce there are no cycles but
for simple cases why not trust the engine?

An analogy here is autonumber generation: sure, there are cases when a
custom order is required but when an incrementing integer is good
enough why bother rolling your own?

I suspect it's not the case that you don't trust the engine, rather
it's simply the case that you've never considered trying it.

Jamie.

--
 
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.
Click to expand...

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.
Click to expand...

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 said:
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.
Click to expand...

You should be 'making it hard' in the front end. The database should be
under complete control of the designer by way of constraints etc.
Making it hard to delete things at the database level only makes it
harder on yourself, the designer (but then you did say you don't trust
yourself said:
Deletions...with maximum
alerts when the data is unrecoverable or expensive.
Click to expand...

There you go. Once you have made them jump through hoops, agree
disclaimers, etc then you are free to delete the data by the most
conducive means. Consider that in a Jet SQL statement you only get to
perform one action e.g.

CREATE PROCEDURE DeleteEmployee
(:SSN CHAR(9))
AS
DELETE FROM Employees
WHERE SSN = :SSN;

If you revoke all permissions then grant execute only to your
application, don't you think it is a positive boon that the related
rows in other tables are immediately affected? it seems to me there is
greater chance of something being left behind if you do the same 'by
hand'.
I barely even trust them to create records! They have to...acknowledge
that if they create a
duplicate I personally will come and bite their legs off
Click to expand...

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 _think_ that ON CASCADE
and its friends are a part of the R model, and probably count as what
Pascal calls "a proprietary extension".
Click to expand...
From reading his website over the years, I've come to the conclusion
Click to expand...
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.

Jamie.

--
 
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.
Click to expand...

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.
Click to expand...

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
 
Back
Top