[Tagging] football or soccer ?
colin.smale at xs4all.nl
Mon Aug 2 16:59:43 BST 2010
On 02/08/2010 17:28, Anthony wrote:
> On Mon, Aug 2, 2010 at 10:56 AM, Anthony<osm at 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.
More information about the Tagging