From: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
---|---|
To: | PGSQL Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Avoiding surrogate keys |
Date: | 2010-04-21 19:17:25 |
Message-ID: | alpine.LNX.2.00.1004211213190.16706@salmo.appl-ecosys.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 21 Apr 2010, Thom Brown wrote:
> I have a mailing list table, and 2 of the columns contain values which
> have to be from a list.
Thom,
From 2 lists?
> These are country and status.
And each is from a separate list, correct?
> 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,
Why? These two fields do not define a unique row, do they? If not, then
neither is a candidate key and should be treated as a regular attribute.
> ... 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.
You could have a table with two columns: abbreviation and name. Then you
could copy that table from the Internet to get the data.
> So instead of
>
> name, email, country, status
> 'mr smith', 'emailaddress(at)example(dot)com', 44, 2
> 'mrs jones', 'me(at)emailcompany(dot)com', 21, 1
Not only is more work, but it's confusing and unnecessary.
> I'd have
>
> name, email, country, status
> 'mr smith', 'emailaddress(at)example(dot)com', 'China', 'Registered'
> 'mrs jones', 'me(at)emailcompany(dot)com', 'Belgium', 'Unconfirmed'
Sure.
> Are there any serious downsides to this? If so, what would you recommend?
Nope. Not even flippant downsides.
Rich
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2010-04-21 19:18:00 | Re: Avoiding surrogate keys |
Previous Message | Joshua D. Drake | 2010-04-21 19:14:21 | Re: Avoiding surrogate keys |