Sql-server – Do I need a separate Id column for this “mapping” table

database-designreferential-integritysql server

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

Then I started to question myself, so I thought I'd ask the experts. Would it be better database design to have an additional Id (int, Primary key) column in my ProducerDetails table? Or is that unnecessary?

I'm using SQL-Server 2008 R2 if that make any difference at all.

EDIT – The relationship between these tables would be many-to-many I believe, sorry I didn't make that clear. A producer can carry multiple types of products, and the same product could be produced by multiple different producers.

I apologize if this question is overly simple, referential integrity / database design is not my strongsuit (although I'm trying to improve that).

Best Answer

If you have a one-to-many relationship between Producers and Products (in other words, a product can only belong to one producer), than it would make sense to just put a foreign key reference directly in your Products table:

One-To-Many

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,
    Name varchar(100) not null,
    ProducerId int not null foreign key references Producer(id)
)
go

But if there's a chance that this will be a many-to-many relationship, then your best bet would be to use a Join table.

Many-To-Many

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 wouldn'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.

Related Question