June 14, 2017 at 2:46 am
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 a wareHouseCode
- Multiple shops have access to multiple warehouses
- Multiple warehouses supply multiple shops
--Fruit Stock levels (PK surrogate key, item and stockcode unique constraint)
DECLARE @Stock TABLE(item VARCHAR(50),stockAmount INT,wareHouseCode VARCHAR(5))
INSERT INTO @Stock( item, stockAmount, wareHouseCode )
VALUES ( 'apples', 10, 'SC01' )
,( 'bananas', 15, 'SC01' )
,( 'apples', 10, 'SC02' )
,( 'oranges', 50, NULL )
----Stock allocated at warehouses. Each warehouse has a code e.g SC01 Shops have access to certain warehouses.
-- Table keys currently (Composite key on code & shop, can change this?)
DECLARE @stockForShop TABLE(wareHouseCode VARCHAR(5),shop VARCHAR(50))
INSERT INTO @stockForShop( wareHouseCode, shop )
VALUES ( 'SC01', 'shop1')
,( 'SC01', 'shop2')
,( 'SC01', 'shop3')
,( 'SC02', 'shop9')
,( 'SC03', 'shop1')
,( 'SC03', 'shop3')
--1 column table to enforce primary and foreign key constraints?
DECLARE @stock_stockForShop TABLE (wareHouseCode VARCHAR(5))
INSERT INTO @stock_stockForShop(wareHouseCode)
VALUES ('SC01'), ('SC02'), ('SC03')
SELECT * FROM @Stock
SELECT * FROM @stockForShop
--Query to get shops that products can sell to and from what warehouse
--using single column table
SELECT * FROM @Stock s
LEFT JOIN @stock_stockForShop ssfs ON s.wareHouseCode = ssfs.wareHouseCode
LEFT JOIN @stockForShop sfs ON ssfs.wareHouseCode = sfs.wareHouseCode
--no single column table
SELECT * FROM @Stock s
LEFT JOIN @stockForShop sfs ON s.wareHouseCode = sfs.wareHouseCode
June 14, 2017 at 4:01 am
In a relational database, you model many-to-many relationships with junction tables, each one with a foreign key relationship to each of the tables it joins.
-- Main tables
CREATE TABLE stock (item VARCHAR(50) NOT NULL, Colour varchar(10) NOT NULL);
ALTER TABLE stock ADD CONSTRAINT PK_stock_item
PRIMARY KEY (item);
INSERT INTO stock( item, Colour )
VALUES ( 'apples', 'green' )
,( 'bananas', 'yellow' )
,( 'oranges', 'orange' );
CREATE TABLE shops (shop char(5) NOT NULL, ShopAddress varchar(10) NOT NULL)
ALTER TABLE shops ADD CONSTRAINT PK_shops_shop
PRIMARY KEY (shop)
INSERT INTO shops (shop, ShopAddress)
VALUES ('shop1', 'New York')
, ('shop2', 'London')
, ('shop3', 'Paris')
, ('shop9', 'Munich');
CREATE TABLE Warehouses (wareHouseCode char(4) NOT NULL, WarehouseName varchar(10) NOT NULL)
ALTER TABLE Warehouses ADD CONSTRAINT PK_Warehouses_wareHouseCode
PRIMARY KEY (wareHouseCode)
INSERT INTO Warehouses (wareHouseCode, WarehouseName)
VALUES ('SC01', 'Freddie')
, ('SC02', 'Tracey')
, ('SC03', 'Jimmy')
-- Junction tables
CREATE TABLE ShopWarehouseAccess (shop char(5) NOT NULL, wareHouseCode char(4) NOT NULL);
ALTER TABLE ShopWarehouseAccess ADD CONSTRAINT PK_ShopWarehouseAccess_shop_wareHouseCode
PRIMARY KEY (shop, wareHouseCode);
ALTER TABLE ShopWarehouseAccess ADD CONSTRAINT FK_ShopWarehouseAccess_shops_shop
FOREIGN KEY (shop) REFERENCES shops(shop);
ALTER TABLE ShopWarehouseAccess ADD CONSTRAINT FK_ShopWarehouseAccess_Warehouses_wareHouseCode
FOREIGN KEY (wareHouseCode) REFERENCES Warehouses(wareHouseCode);
INSERT INTO ShopWarehouseAccess (wareHouseCode, shop)
VALUES ( 'SC01', 'shop1')
,( 'SC01', 'shop2')
,( 'SC01', 'shop3')
,( 'SC02', 'shop9')
,( 'SC03', 'shop1')
,( 'SC03', 'shop3');
CREATE TABLE WarehouseStock (wareHouseCode char(4) NOT NULL, item varchar(50) NOT NULL, Quantity int NOT NULL);
ALTER TABLE WarehouseStock ADD CONSTRAINT PK_WarehouseStock_wareHouseCode_item
PRIMARY KEY (wareHouseCode, item);
ALTER TABLE WarehouseStock ADD CONSTRAINT FK_WarehouseStock_Warehouses_wareHouseCode
FOREIGN KEY (wareHouseCode) REFERENCES Warehouses(wareHouseCode);
ALTER TABLE WarehouseStock ADD CONSTRAINT FK_WarehouseStock_Stock_item
FOREIGN KEY (item) REFERENCES Stock(item);
INSERT INTO WarehouseStock (item, Quantity, wareHouseCode)
VALUES ( 'apples', 10, 'SC01' )
,( 'bananas', 15, 'SC01' )
,( 'apples', 10, 'SC02' )
,( 'oranges', 50, 'SC03' );
Edit - changed "one-to-one" to "many-to-many".
John
June 14, 2017 at 5:22 am
Thanks for this John, explains things a lot better...I think I was approaching the schema design wrong.
Another common scenario is that an item might not have a warehouse assigned and the Quantity is available to all shops.
Do you think that could potentially change the design or is having a NULL for wareHouseCode FK alright?
e.g:
INSERT INTO WarehouseStock (item, Quantity, wareHouseCode)
VALUES ( 'apples', 10, 'SC01' )
,( 'bananas', 15, 'SC01' )
,( 'apples', 10, 'SC02' )
,( 'oranges', 50, 'SC03' );
,( 'pears', 50, NULL );
June 14, 2017 at 5:31 am
I'm not sure I understand the business logic behind that requirement. Would the 50 pears be stashed in somebody's spare room? Possibly the best way to model this is to add a dummy warehouse to your Warehouses table, and assign all unassigned fruit boxes to that. A NULL might work, though - have you tried it? I'm sure a relational purist will chip in if that violates one of the normal forms.
John
June 14, 2017 at 5:44 am
John Mitchell-245523 - Wednesday, June 14, 2017 5:31 AMI'm not sure I understand the business logic behind that requirement. Would the 50 pears be stashed in somebody's spare room? Possibly the best way to model this is to add a dummy warehouse to your Warehouses table, and assign all unassigned fruit boxes to that. A NULL might work, though - have you tried it? I'm sure a relational purist will chip in if that violates one of the normal forms.John
I have tried it removing the PK constraint and changing it to a unique index with a surrogate PK. it works with some thoughtful where conditions
The reason for this is there is actually an extra column in WarehouseStock like a a parent warehouse and not all of these parent warehouses have sub warehouses
INSERT INTO WarehouseStock (item, Quantity, parentWareHouseCode, wareHouseCode)
VALUES ( 'apples', 10, 'WH01', 'SC01' )
,( 'bananas', 15, 'WH01', 'SC01' )
,( 'apples', 10, 'WH01', 'SC02' )
,( 'oranges', 50, 'WH01', 'SC03' );
,( 'pears', 50, 3,'WH02', NULL );
June 14, 2017 at 5:57 am
Yikes! Now we're into hierarchies, which makes things a whole lot more complicated. Let me have a think. There may not be an easy way to do this. I think that column should be in the Warehouses table, not WarehouseStock, if the same warehouse always has the same parent warehouse.
John
June 14, 2017 at 6:44 am
John Mitchell-245523 - Wednesday, June 14, 2017 5:57 AMYikes! Now we're into hierarchies, which makes things a whole lot more complicated. Let me have a think. There may not be an easy way to do this. I think that column should be in the Warehouses table, not WarehouseStock, if the same warehouse always has the same parent warehouse.John
- Currently this is the setup, parentWarehouseCode is in the existing table WarehouseStock i'm adding in this "subWareHouseCode."
- Yes the child warehouse does always have the same warehouse parent. Its a 1 to many / parent to child
June 14, 2017 at 7:22 am
WarehouseStock is the wrong place for it, since it breaks one of the rules of normalisation by introducing redundancy. If you know that WH01 is the parent of SC02, you don't need to repeat that in every row. It's an attribute of the warehouse SC02, and hence the Warehouse table is where the column should go. You can model the hierarchy with a self-referencing foreign key:
CREATE TABLE Warehouses (wareHouseCode char(4) NOT NULL, WarehouseName varchar(10) NOT NULL, ParentCode char(4) NULL)
ALTER TABLE Warehouses ADD CONSTRAINT PK_Warehouses_wareHouseCode
PRIMARY KEY (wareHouseCode);
ALTER TABLE Warehouses ADD CONSTRAINT FK_Warehouses_Warehouses_ParentCode
FOREIGN KEY (ParentCode) REFERENCES Warehouses(wareHouseCode);
INSERT INTO Warehouses (wareHouseCode, WarehouseName, ParentCode)
VALUES ('SC01', 'Freddie', 'WH01')
, ('SC02', 'Tracey', 'WH01')
, ('SC03', 'Jimmy', 'WH01')
, ('WH01', 'Wendy', NULL)
, ('WH02', 'Billy', NULL);
John
June 14, 2017 at 8:17 am
I'm with you. This is a cleaner approach didn't even think of this.
I know redundancy is not ideal but in some situations is it not acceptable especially when it comes to performance, de-normalizing the data can help?
June 14, 2017 at 8:31 am
Redundancy is indeed not ideal. Not only does it take up more space on disk, but it also doesn't stop someone from adding a row to the ParentWarehouse table with SC01 as the warehouse and WH02 as the parent. Denormalising for performance is sometimes appropriate, but that's usually for a data warehouse situation (unfortunate clash of terminology there!), not an OLTP database.
John
June 14, 2017 at 9:30 am
bugg - Wednesday, June 14, 2017 5:22 AMAnother common scenario is that an item might not have a warehouse assigned and the Quantity is available to all shops.
Do you think that could potentially change the design or is having a NULL for wareHouseCode FK alright?
If you define a NULL as 'I don't know', you can see that using a NULL value in this situation does not fit. In your scenario, you know the warehouse assignment (none or n/a), which is a very different meaning from 'I don't know'.
For data quality issues, I never design assigning a knowable value to NULL. It is much safer and more accurate to create a placeholder or dummy record.
Wes
(A solid design is always preferable to a creative workaround)
June 14, 2017 at 9:41 am
The Null for the warehouse code seems to me to be an issue as well. If warehouses can have a parent warehouse, and that parent warehouse can also store items, this seems to me on the face of it like the employee manager relationship. All the warehouses are present in the table and can have stock assigned to them. There is also an additional column to indicate their parent (if any) warehouse. Does that seem to make sense for your situation?
June 14, 2017 at 10:20 am
RonKyle - Wednesday, June 14, 2017 9:41 AMThe Null for the warehouse code seems to me to be an issue as well. If warehouses can have a parent warehouse, and that parent warehouse can also store items, this seems to me on the face of it like the employee manager relationship. All the warehouses are present in the table and can have stock assigned to them. There is also an additional column to indicate their parent (if any) warehouse. Does that seem to make sense for your situation?
Yes this it what i had just in the wrong table.. John Mitchell-245523 suggested a new table that stores the child parent relationship with self referencing FK
June 14, 2017 at 10:45 am
John Mitchell-245523 suggested a new table
I don't know that it needs a new table, although neither way is wrong. It depends on other things. But having them in the same table with a column referencing the parent makes it easier to assign goods to the parent. Otherwise it seems to me that you'd have to join the goods table to both the warehouse child and the warehouse parent table.
June 15, 2017 at 1:44 am
RonKyle - Wednesday, June 14, 2017 10:45 AMJohn Mitchell-245523 suggested a new table
I don't know that it needs a new table, although neither way is wrong. It depends on other things. But having them in the same table with a column referencing the parent makes it easier to assign goods to the parent. Otherwise it seems to me that you'd have to join the goods table to both the warehouse child and the warehouse parent table.
Yes I would have to join the goods table to both the warehouse child and the warehouse parent table, that is how i had it originally. 2 separate tables, which actually makes it easier as the current production the setup is using this master warehouse, so requires less changes. The introduction of child warehouse is new, so now I'm deciding whether to create this all in the one table or keep them separate
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply