Foreign key questions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil Latio - 26 Aug 2004 06:55 GMT

1. Is it bad design to have a table with two foreign keys that reference the
same table? For example a football player transfer table which would have
columns for both the leaving and joining club.

2. Are there any other methods of referencing other than foreign keys and
using SELECT statements to ensure data integrity? For example with SELECT
statements, a form with list box prefilled so only values matching that
statement appear.

Cheers

Phil

Reply to this Message

John - 26 Aug 2004 08:44 GMT

> 1. Is it bad design to have a table with two foreign keys that reference the
> same table? For example a football player transfer table which would have
> columns for both the leaving and joining club.

No.

> 2. Are there any other methods of referencing other than foreign keys and
> using SELECT statements to ensure data integrity? For example with SELECT
> statements, a form with list box prefilled so only values matching that
> statement appear.

You should enforce integrity wherever you can. I would /aim/ to create
constraints in the database to ensure that it is impossible for the
front end (your list boxes etc) to make a mess of it. I would also /aim/
to design the front end such that it is impossible for the user to cause
rubbish to be passed to the database. If you get reasonably to close to
both your objectives, your data is likely to stay in fairly good order,
and the user will experience few errors and little confusing behaviour.

> Cheers
>
> Phil

Reply to this Message

Tony - 26 Aug 2004 14:06 GMT

No, it is good design.

No, you are confusing integrity constraints (foreign keys) with the
user interface (list boxes). You may well want to provide list boxes
in your application, but should still have the constraints in the
database as well.

Reply to this Message