[Tagging] football or soccer ?

Colin Smale 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 mailing list