Re: foreign key, or "in" constraint?

Mark D Powell (Mark.Powell_at_eds.com) wrote:
: On Oct 6, 4:39=A0pm, m..._at_pixar.com wrote:
: > Suppose you have a table with a status column, and
: > the status can be one of "queued", "inprogress",
: > "failed", or "completed".
: >
: > This can be enforced two ways:
: >
: > 1. =A0status has a FK to a "statuses" table, and the allowed statuses
: > =A0 =A0 are in that table.
: >
: > 2. =A0with a constraint such as
: > =A0 =A0 =A0 =A0 status in ('queued','ip','failed','completed')
: >
: > Which is more typically used? =A0What are the pros and cons,
: > tradeoffs, etc, of each?
: >
: > Many TIA!
: > Mark
: >
: > --
: > Mark Harrison
: > Pixar Animation Studios

: The pros of the column constraint approach is you do not need a second
: table.

: The cons of the column constraint approach is that the values are
: basically hard coded and if you need to apply the same restriction to
: another table you now have two or more places that have to be changed
: in the event you add or remove another value.

: I think the key questions are: How likely are the values to every
: change and on how many different tables would you need to actually
: check that the value is valid? The closer the anwers are to never and
: one the more applicable the column constraint is.

: HTH -- Mark D Powell --

If you need to read the values then the FK can be useful.

For example a report that summarizes the data by status needs to loop over the FK table because otherwise it can't report on a status that has no entries - unless you hard code the possible values into the report logic.

Same issue in a data entry form, if you don't have an FK table then how do you build an LOV to select the status ? -- you have to hard code the values in the LOV if you don't have a list to read.

$0.10 Received on Mon Oct 06 2008 - 19:47:45 CDT