Do I need a separate Id column for this "mapping" table?

Question

I have a table of Producers and a table of Products, both of which are of the form:

• Id - int, Primary key
• Name - nvarchar

A Producer can carry multiple Products, so I was going to create a table called ProducerDetails that would have:

• ProducerId - int, Foreign key to Producers.Id
• ProductId - int, Foreign key to Products.Id

Would it be better database design to have an additional Id (int, Primary key) column in my ProducerDetails table?

I'm using SQL Server 2008 R2.

The relationship between these tables is many-to-many. A producer can carry multiple types of products, and the same product could be produced by multiple different producers.

Answer 1

For a many-to-many relationship, your best bet is to use a join table.

create table Producer
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table Product
(
    id int identity(1, 1) not null primary key clustered,
    Name varchar(100) not null
)
go

create table ProductProducer
(
    ProductId int not null foreign key references Product(id),
    ProducerId int not null foreign key references Producer(id)
)
go

-- adding the primary key also ensures uniqueness
alter table ProductProducer
add constraint PK_ProductProducer 
primary key (ProductId, ProducerId)
go

If you decide to go with the join table, you won't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique.

You could use them as a composite key, so you wouldn't need that additional Id field in ProductProducer.

Answer 2

No, there is no value in adding an additional "primary key" to this table. Your joins are only ever going to refer to ProducerID and ProductID, so it is just dead weight. IMHO.

Though I agree with @Shark that the join table doesn't even seem to be needed here, unless you are going out of your way to not change the schema of the existing tables in any way.

As an aside, I also think it is worthwhile to name your primary identifier in full (e.g. Products.ProductID instead of Products.ID) so that the identifier is consistently named throughout the schema.