2

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

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

CREATE TABLE StackBoxItem
( StackBoxID INT NOT NULL, 
  ItemID INT NOT NULL, 
  Position INT NOT NULL,
  PRIMARY KEY (StackBoxID, Position),
  UNIQUE (StackBoxID, ItemID),
  FOREIGN KEY (StackBoxID) 
    REFERENCES StackBox (StackBoxID),
  FOREIGN KEY (ItemID) 
    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? – displayname Apr 29 '15 at 17:43
  • Yes, it doesn't matter which is the Primary Key and which is the Unique constraint. – ypercubeᵀᴹ Apr 29 '15 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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