Joe Joe - 18 days ago 7
SQL Question

Do I need a surrogate key in a one-to-many relationship?

create table A (
id int(10) not null,
val1 varchar(255),
primary key (id)
);


Approach [a]:

create table B (
a_id int(10) not null,
val2 varchar(255),

foreign key (a_id) references A(id)
);


Approach [b]:

create table B (
id int(10) not null,
a_id int(10) not null,
val2 varchar(255),

foreign key (a_id) references A(id),
primary key (id)
);


By choosing [a], I can avoid creation of the "id" surrogate key in table 'B'. Which is the preferred approach for creating table 'B' from a modeling perspective?

Answer

As i understand it : in [a], you are creating a 1:1 relationship, in [b] you are not. They aren't alternatives!

In case [b], if table A would hold invoices, table B could be used for invoicelines whereas in [a] this cannot, since there can only be one record for each record in table A. (Thus only 1 invoiceline per invoice)

So, if you really want an answer, use [b], your [a] construct could be replaced with one table only, and is probably not what you mean. (also since you didn't set a primary key, the same as the FK in a 1:1 relationship)

Comments