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

Question

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

[a] create table B (a_id int(10) not null, val2 varchar(255), foreign key (a_id) references A (id));

[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 1

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)

Answer 2

For surrogate keys in general,

A professor in my Computer Science course says "No".

Practical experience for me, says, yes.

I rather use one despite the increase space just for the sake of easy to read SQL statements, and more flexibility in case of requirement changes.

Answer 3

You never need a surrogate key (hence it's name). It looks like you're mixing a logical model and a physical model. For your logical model, you presumably would have

CREATE TABLE A (
    Val1 varchar() not null,
    constraint PK_A KEY (Val1) --?
)

CREATE TABLE B (
    Val1 varchar() not null,
    Val2 varchar() not null,
    constraint PK_B KEY (Val2), --? or Val1,Val2?
    constraint FK_A FOREIGN KEY (Val1) references A
)

(The above in made-up SQL, but hopefully you see the point)

Now, for a physical model, you might introduce surrogates wherever they make sense - where the logical keys are long (e.g. varchars). But whether you do or not is really up to you. And remember to enforce the logical keys still