On 02/08/2010 17:28, Anthony wrote:
On Mon, Aug 2, 2010 at 10:56 AM, Anthony<o...@inbox.org> wrote:
If I wanted the list of colors to be controlled and
finite I'd use a check constraint.
By the way, if I were going to use a separate table for the list of
colors, for instance for performance purposes, I'd still use the name
of the color as the key, thus avoiding doing a join every single time
you do a select. The lookup would only be necessary when doing adds,
in order to check the foreign key constraint. The lookup table on
color would have only one column, the color name.
Fair enough as an optimisation, if it didn't compromise functionality.
Which language would you use for the key values? This discussion started
about normalisation of different names (soccer, football,
association_football etc) for the same thing (that game, whatever you
call it). Whatever language you choose for the FK it will not suit
everybody; applications will (should?) end up doing an additional select
to translate that value to the appropriate locale anyway. You (en_US)
prefer "soccer", I (en_GB) would prefer "football". Using a text value
for the field is of course only one step away from using an integer...
Constraints are all very well as a "last resort" way of ensuring only
valid data gets stored where the criteria are set in stone. The problem
I have with Constraints for this purpose is that the list of valid
values has to be maintained in two places - once in the constraint
definition, and once in the application code where it presents a list to
the user during "data entry." Using a foreign key for referential
integrity allows a dynamic self-maintaining link between the allowed
values and the user interface.
_______________________________________________
Tagging mailing list
Tagging@openstreetmap.org
http://lists.openstreetmap.org/listinfo/tagging
