Assume I have a Table StackBox that holds items as Item. I allow StackBox to have multiple Item and I allow Item to be in different StackBox. It is obviously an m-to-n relation. Solving this with an according m-to-n mapping table is easy.

But my problem here is the thing that I want to store the information in which order many Item are stored inside a StackBox. I could e.g. add a column to the m-to-n mapping table. Is this a valid solution even though I never want to ask "At which places does Item occur"? I am only interested in "At which place inside StackBox is Item with id x"?

The other option would be to create two additional tables of course. One for the mappings from StackBox to Item that just contains two index tables and another one for the mappings from Item to StackBox that contains the two index columns plus e.g. an additional column position.

I hope my question is clear. Please let me know if I should clarify anything.


1 Answer 1


I could e.g. add a column to the m-to-n mapping table. Is this a valid solution?

Yes, this solution desrcibes best your scenario. A table with (StackBox, Item, Position) and a unique constraint on (StackBox, Position) (meaning only one item in each place/position) and probably another unique constraint on (StackBox, Item) (unless you can have an item twice or more in a stackbox).

The table would be like (I assume the other two tables have primary keys StackBoxID and ItemID and are INT, adjust accordingly to your design):

  Position INT NOT NULL,
  PRIMARY KEY (StackBoxID, Position),
  UNIQUE (StackBoxID, ItemID),
    REFERENCES StackBox (StackBoxID),
    REFERENCES Item (ItemID)
) ;
  • Thank you for answering! But woudn't it be PRIMARY KEY (StackBoxID, ItemID) and UNIQUE KEY (StackBoxID, Position)? Where StackBoxID and ItemID are according foreign keys. That way I would have an m-to-n relation and would ensure unique appearance of Item in StackBox - which I too would like to ensure - didn't mention that in my question? Apr 29, 2015 at 17:43
  • Yes, it doesn't matter which is the Primary Key and which is the Unique constraint. Apr 29, 2015 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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