1

For a store I have many store_offers which is a one-to-many relationship.

However, for a table

create table store (    
  id bigserial primary key
);

I can use a single primary key id (SQLfiddle):

create table store_offer (

  id bigserial primary key,

  store_id bigint not null,

  constraint fk__store_offer__store
    foreign key (store_id)
    references store(id)    
);

or a composite primary key (id, store_id) (SQLFiddle):

create table store_offer (

  id bigserial not null,

  store_id bigint not null,

  constraint fk__store_offer__store
    foreign key (store_id)
    references store(id),

  primary key(id, store_id)    
);

My question is "what does make more sense here?". Imho the composite key should be the correct way to do it since a store_offer is actually "bound" to as store. But one can argue that this is already the case since the first version has a foreign key. On the other hand a store_offer primary key actually must not change once it's created. You have to create a new store_offer and delete the old one if you want discard one. But you cannot simply change store_id in the second approach.

So what is the correct answer here?

Stefan Falk
  • 23,898
  • 50
  • 191
  • 378
  • I'm not sure if this question is unique, to me it seems to be the general "surrogate vs composite" key question. There's a pretty good answer [here](https://stackoverflow.com/a/23851003/352349). – Anssssss Mar 22 '16 at 16:43
  • I think that it boils down to whether or not store offer's id column should be unique, or should it only be unique within a specific store. – Zohar Peled Mar 22 '16 at 17:47
  • @ZoharPeled Yes, I think this is why I prefer the second approach. – Stefan Falk Mar 22 '16 at 18:01
  • Well, if the ids should be only unique within a specific store, then the second approach is the only way to do it that I'm aware of. – Zohar Peled Mar 22 '16 at 18:05
  • @ZoharPeled The link posted by Anssssss states that this is also useful for row level security. I am using PostgreSQL which has that since 9.5 afaik. I also plan to implement row-level security but actually I thought that Spring Security is going to help me with that - but I won't if I can do this actually in the database layer. – Stefan Falk Mar 22 '16 at 18:17

1 Answers1

0

Using primary key(id, store_id) is a bad idea. This will make many queries more complicated and more prone to error. It sounds like what you are really trying to make is a many-to-many relationship between stores and offers. If this is the case you should have a store table with unique store_id as a primary key, an offer table with unique offer_id as a primary key and a store_offer table that has a primary key of store_id and offer_id.

John
  • 3,458
  • 4
  • 33
  • 54
  • I don't get this. There will never be the case where I don't have id or store_id so the only thing that might make it more complicated would be that I have to specify id and store_id in order to update a single record if I am not mistaken. I am very certain that I don't need a many-to-many relationship here because every shop has its own offers. Even though two shops sell the thing e.g. a PlayStation they might have a different price, name or description etc.or am I getting this wrong? :/ – Stefan Falk Mar 22 '16 at 19:00
  • If that is the case than you should have store_offer with offer_id as the primary key and store_id as a foreign key to the store table. The composite key will only serve to complicate things. – John Mar 22 '16 at 19:08