Design for m-to-n relation with unidirectional attribute(s) 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.
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)
) ;