Re: surrogate vs natural primary keys

On Wed, Sep 17, 2008 at 7:20 AM, Seb <spluque(at)gmail(dot)com> wrote:
> On Tue, 16 Sep 2008 20:34:51 -0600,
> "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> [...]
>
>> create table t2 (
>> d1 varchar(200),
>> d2 int8,
>> d3 varchar(1000),
>> foreign key t2_fk references t1(c1,c2) );
>
> Thanks Scott, I guess you meant:
>
> CREATE TABLE t2 (
> d1 varchar(200),
> d2 int8,
> d3 varchar(1000),
> PRIMARY KEY (d1, d2)
> FOREIGN KEY (d1, d2) REFERENCES t1(c1, c2) );
>
> But this makes it difficult to work with t2 because it has 2 fields that
> are the same as in t1. Isn't it better to just use a surrogate key and
> use a single field in t2, thereby avoiding repeating multiple pieces of
> information?

Yes and no. If you're gonna hit table t2 a lot by itself, then it's
more efficient to have the data there in t2 and not have to join to t1
to get it. There are always use cases that go either way in this kind
of situation.