On Mon, Aug 2, 2010 at 11:59 AM, Colin Smale <colin.sm...@xs4all.nl> wrote: > 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...
I'd use English. Preferably US English, but as British English is the de facto standard I don't have a problem with that. In any event, I'd definitely avoid using ambiguous terms like "football" (unless the intent was to be ambiguous, e.g. a generic field which is used for all different sorts of "football"). I'd say "soccer" is fine, but if it's legitimately offensive I'm open to other suggestions. Wikipedia uses "association football", and copying names from Wikipedia is another common OSM thing. > 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. I wouldn't use constraints for this purpose. But then, this is not an instance of wanting the list to be controlled and finite. As for maintaining the list in two places, that isn't strictly necessary. The application can just get the list from the database when it starts up, and cache it for future use. If the list is dynamic, then the application has to get the list every time. In either case though, that really has nothing to do with the constraint. Unless you're suggesting that the constraint be put *only* in the application, and not in the db at all. In which case, that's all fine and dandy until you have a second application using the same database. _______________________________________________ Tagging mailing list Tagging@openstreetmap.org http://lists.openstreetmap.org/listinfo/tagging
