Hi, I think nobody mentioned Object-Relational mappers. If you intend to used one (or think you may be using one in the future), using surrogate keys is more straightforward, if not necessary.
Best regards, ------------------------------------------------------------- Attik System web : http://www.attiksystem.ch Philippe Lang phone: +41 26 422 13 75 rte de la Fonderie 2 gsm : +41 79 351 49 94 1700 Fribourg pgp : http://keyserver.pgp.com > -----Message d'origine----- > De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] De la part de Thom Brown > Envoyé : mercredi 21 avril 2010 21:01 > À : PGSQL Mailing List > Objet : [GENERAL] Avoiding surrogate keys > > I think I know what I plan to do, but want to throw this out there to > see if there are differing points of view. > > I have a mailing list table, and 2 of the columns contain values which > have to be from a list. These are country and status. There are 237 > possible countries and 3 possible statuses. Now I know some people > would assign a sequence ID (surrogate key) to the country and status > values, and have them looked up in separate tables to get the textual > value, but I think I'll still have those tables, just without an ID > column, so 1 column for both the countries and statuses tables. This > means storing the proper value in the main table. > > So instead of > > name, email, country, status > 'mr smith', 'emailaddr...@example.com', 44, 2 > 'mrs jones', 'm...@emailcompany.com', 21, 1 > > I'd have > > name, email, country, status > 'mr smith', 'emailaddr...@example.com', 'China', 'Registered' > 'mrs jones', 'm...@emailcompany.com', 'Belgium', 'Unconfirmed' > > The values of course would be constrained by foreign key lookup to > their associated tables. > > Are there any serious downsides to this? If so, what would you > recommend? > > Thanks > > Thom
PGP.sig
Description: PGP signature
