[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