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. status has a FK to a "statuses" table, and the allowed statuses
are in that table.

2. with a constraint such as
status in ('queued','ip','failed','completed')

Which is more typically used? What are the pros and cons,
tradeoffs, etc, of each?

Many TIA!
Mark

--
Mark Harrison
Pixar Animation Studios

On Oct 6, 4:39*pm, m... (AT) pixar (DOT) com wrote:
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 --

On Oct 6, 4:39*pm, m... (AT) pixar (DOT) com wrote:
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 --

On Oct 6, 4:39*pm, m... (AT) pixar (DOT) com wrote:
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 --

On Oct 6, 4:39*pm, m... (AT) pixar (DOT) com wrote:
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 --

Mark D Powell (Mark.Powell (AT) eds (DOT) com) wrote:
: On Oct 6, 4:39=A0pm, m... (AT) pixar (DOT) 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

Mark D Powell (Mark.Powell (AT) eds (DOT) com) wrote:
: On Oct 6, 4:39=A0pm, m... (AT) pixar (DOT) 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

Mark D Powell (Mark.Powell (AT) eds (DOT) com) wrote:
: On Oct 6, 4:39=A0pm, m... (AT) pixar (DOT) 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

Mark D Powell (Mark.Powell (AT) eds (DOT) com) wrote:
: On Oct 6, 4:39=A0pm, m... (AT) pixar (DOT) 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

Thanks Mark and Malcolm,
That really clarified my thinking.

--
Mark Harrison
Pixar Animation Studios