the rule of thumb for me is:
- if you have more than one column as PK - and are variable length,
or more than 2 columns, fixed length, no bigger than 8 bytes - go for
surrogate - always.
- if PK is variable length, on average longer than 8 bytes, or can
change - go surrogate.
- Otherwise leave
2010/5/4 Grzegorz Jaśkiewicz :
>
> Another thing, If your PK changes, it is no longer a PK, you can't rely on it.
Depending upon what you mean by changes this could be true or it could
be a matter of opinion. If your referring to a candidate key's value
changes, this key still provides a useful w
On Tue, May 4, 2010 at 3:16 PM, Merlin Moncure wrote:
> On Tue, May 4, 2010 at 9:40 AM, Merlin Moncure wrote:
>> On Sat, May 1, 2010 at 4:14 PM, John R Pierce wrote:
>>>
>>> If your 'natural key' is a large text field, I'd have to assume there's some
>>> point at which a surrogate index would be
On Tue, May 4, 2010 at 9:40 AM, Merlin Moncure wrote:
> On Sat, May 1, 2010 at 4:14 PM, John R Pierce wrote:
>>
>> If your 'natural key' is a large text field, I'd have to assume there's some
>> point at which a surrogate index would be more efficient. Would this be
>> above a few dozen characte
On Sat, May 1, 2010 at 4:14 PM, John R Pierce wrote:
>
> If your 'natural key' is a large text field, I'd have to assume there's some
> point at which a surrogate index would be more efficient. Would this be
> above a few dozen characters, or a few 100 characters? I wouldn't want a
> PK based o
If your 'natural key' is a large text field, I'd have to assume there's
some point at which a surrogate index would be more efficient. Would
this be above a few dozen characters, or a few 100 characters? I
wouldn't want a PK based on a multi-K byte text field for a table that
has many 10s
On Sat, May 1, 2010 at 12:09 PM, Lew wrote:
> Philippe Lang wrote:
>>
>> 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.
>
> Neither of those claims is
Philippe Lang wrote:
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.
Neither of those claims is even slightly true. Using Hibernate, EclipseLink
or Ope
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
nvoyé : 21 avril 2010 16:38 À : wmo...@potentialtech.com Cc :
pgsql-general@postgresql.org Objet : Re: [GENERAL] Avoiding surrogate keys
...
static information such as country names *should be* de-normalised into
non-indexed columns of the driving table as you have already done
if on the other hand
.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Avoiding surrogate keys
>
> In response to Thom Brown :
>
> > 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 maili
On Wed, Apr 21, 2010 at 3:32 PM, Bill Moran wrote:
> In response to "Joshua D. Drake" :
>
>> On Wed, 2010-04-21 at 15:07 -0400, Bill Moran wrote:
>> > I'd use an ENUM for the status, as that's not liable to change.
>> >
>> > The only problem I see with avoiding the surrogate key for the country
>>
Bill Moran escribió:
> One thing that a lot of people seem to get confused about is that they
> subconsciously think that ints or bigints take up less space when the
> numbers are small. I.e.: I want to use an int for my state identifier
> instead of the 2-digit code, because it will use less spa
In response to "Joshua D. Drake" :
> On Wed, 2010-04-21 at 15:07 -0400, Bill Moran wrote:
> > I'd use an ENUM for the status, as that's not liable to change.
> >
> > The only problem I see with avoiding the surrogate key for the country
> > is that the table might require more disk space if a lot
On 21 April 2010 20:18, Merlin Moncure wrote:
> On Wed, Apr 21, 2010 at 3:01 PM, Thom Brown wrote:
> > 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
On Wed, 2010-04-21 at 15:18 -0400, Merlin Moncure wrote:
> Natural keys:
> *) force formal relationships into your key design (this is good)
> *) Make your database MUCH easier to follow, browse, and understand
> *) in particular cases allow you to skip joins
> *) will make your indexes fatter (th
On Wed, Apr 21, 2010 at 3:01 PM, Thom Brown wrote:
> 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.
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
On Wed, 2010-04-21 at 15:07 -0400, Bill Moran wrote:
> I'd use an ENUM for the status, as that's not liable to change.
>
> The only problem I see with avoiding the surrogate key for the country
> is that the table might require more disk space if a lot of the country
> names end up being very long
In response to Thom Brown :
> 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 possi
20 matches
Mail list logo
