Many-to-many relationship with a one column ‘junction’ table? Basically trying to get my head around how to enforce primary / foreign key constraints on what is essentially a many-to-many (M:N) relationship. Or if I should ignore them in this instance... 

The schema design below is based on the following. 


Shops sell fruit (item)
The fruit for each shop can be retrieved from certain warehouses that have stockcodes
Multiple shops have access to multiple warehouse
Multiple warehouses supply multiple shops






--Fruit Stock levels (PK surrogate key, item and stockcode unique constraint)
DECLARE @stock TABLE(item VARCHAR(50),stockAmount INT,stockCode VARCHAR(5))
INSERT INTO @stock( item, stockAmount, stockCode )
VALUES   ( 'apples',  10, 'SC01' )
        ,( 'bananas', 15, 'SC01' )
        ,( 'apples',  10, 'SC02' )
        ,( 'oranges', 50, NULL ) --no stock code as 50 avaibale from any warehouse 

--Stock allocated into stockpiles/warehouses. Each warehouse has a code e.g SC01 Shops have access to certain warehouses. Table keys currently (Composite key on code &amp; shop, can change this?)

DECLARE @stockForShop TABLE(code VARCHAR(5),shop VARCHAR(50))
INSERT INTO @stockForShop( code, shop )
VALUES   ( 'SC01', 'shop1')
        ,( 'SC01', 'shop2')
        ,( 'SC01', 'shop3')
        ,( 'SC02', 'shop9')
        ,( 'SC03', 'shop1')

--1 column ‘junction’ table to enforce primary and foreign key constraints
DECLARE @stock_stockForShop TABLE (code VARCHAR(5))
INSERT INTO @stock_stockForShop(code)
VALUES ('SC01'), ('SC02'), ('SC03')

--SELECT  * FROM @stock
--SELECT  * FROM @stockForShop

--Query to get shops and what stockpile the products can sell to via  from
--using ‘junction’ table (enforce primary key on actual tables)
SELECT * FROM @stock s 
LEFT JOIN @stock_stockForShop ssfs ON s.stockCode = ssfs.code
LEFT JOIN @stockForShop sfs ON ssfs.code = sfs.code

--no ‘junction’ table
SELECT * FROM @stock s 
LEFT JOIN @stockForShop sfs ON s.stockCode = sfs.code
I would include the table you're calling @stock_stockForShop (though I might change the name to something like @stockpile or even @warehouse if that actually makes sense in context.

A stockCode represents a unique entity in your schema; it defines a set of inventory that is available to a set of stores. If you do not store it and enforce it as a foreign key, you have to allow new codes to be created via @stock and @stockForShop, meaning that a typo could create a stockCode that is not used anywhere else, leaving the associated stock to potentially go to waste. This is, perhaps, the most important point - ensuring that all stockCodes are well established and usable, and that new ones are only created deliberately.

In addition, a stockCode might also have important attributes that you might want to be able to search on from an application: it could be tied to a specific geographic location (such that it would make sense to connect to shops within 100 miles of it, but no farther), or contact info (phone/fax/email).

So, I would have the @stock_stockForShop table, and would make IDs referring to other tables as foreign keys (again, making sure that typos don't create virtual new stockCodes).

Terminology note: I would consider @stockForShop to be your linking/mapping table (which is how I read "junction"), as it ties a shop to one or more warehouses, and a warehouse to one or more shops.