Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
1  
You don't answer the actual question though - he is asking is there any value in having an id field in his relation table? –  JNK Mar 30 '12 at 17:17
    
@JNK I have edited my question. If ProductId, ProducerId is a unique combination, I don't see the need to add another artificial key to the Join table. Agreed? And I think, unless I'm misunderstanding the question, the OP doesn't even need to use a Join table for this use-case. –  Thomas Stringer Mar 30 '12 at 17:20
    
Ah, I did not make that clear. Yes, there is the relationship would be (I think) many to many. A product could be produced by more than one producer, and a producer and can carry multiple products. –  jadarnel27 Mar 30 '12 at 17:24
    
@jadarnel27 Ok, thank you for the clarification. I have crossed-out that portion of my answer (though I think it prudent to have some footprint for further reference). –  Thomas Stringer Mar 30 '12 at 17:28
    
"you wouldn't need to have an additional key, as the combination of ProductId/ProducerId would ultimately be unique" - Ah, that makes sense! Exactly what I was wondering about. –  jadarnel27 Mar 30 '12 at 17:35
add comment

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.

share|improve this answer
    
+1, thanks! Question regarding your last paragraph: I thought it was bad practice to include the name of the table before the column name (Products.ProductId, rather than just using Products.Id). Am I wrong (or perhaps confused) about that? –  jadarnel27 Mar 30 '12 at 17:40
    
@jadarnel27: For all other columns, yes, it is considered bad practise. For the PK column, many prefer to use this style (ProductID). One advantage is that when you see an SometableID, you know immediately which table it refers to. Another is that you can use the Product JOIN ProducerDetail USING(ProductID) syntax, instead of the longer Product JOIN ProducerDetail ON Product.ID = ProducerDetail.ProductID –  ypercube Mar 30 '12 at 18:17
    
Sorry, I think the USING(ProductID) is not available in SQL-Server, so that point doesn't apply. –  ypercube Mar 30 '12 at 18:24
    
@ypercube Thanks for clarifying that. I can see how that would be more clear. And, as Aaron said, it would be consistent across all tables (which is nice). –  jadarnel27 Mar 30 '12 at 18:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.