Firstly, database novice here. I'm trying to create a system for processing Sales Orders into Purchase Orders and then split the Purchase Order into Shipping Containers. The systems needs to be able to split a Sales Order Line Item into 2 or more Purchase Orders and a Purchase Order Line Item into 2 or more Shipping Containers.
There will be some(a lot of) adjustments back and forth where once a Sales Order Line Item gets split and put into multiple Purchase Orders it might get split differently later on. And same when a Purchase Order Line Item is processed into Shipping Containers. I can't for the life of me how to best handle this operation.
If it helps, here are the tables I have so far with some details excluded for brevity:
CREATE TABLE PurchaseOrder (
Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
PurchaseOrderNumber VARCHAR(15) UNIQUE,
PRIMARY KEY(Id)
);
CREATE TABLE Container (
Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
ContainerName VARCHAR(20) UNIQUE,
PurchaseOrderId INTEGER UNSIGNED,
PRIMARY KEY(Id),
FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),
);
CREATE TABLE SalesOrder (
Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
ClientId INTEGER UNSIGNED,
SalesOrderNumber VARCHAR(10),
PRIMARY KEY(Id),
FOREIGN KEY(ClientId) REFERENCES Client(Id)
);
CREATE TABLE SalesOrderLineItem (
Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
SalesOrderId INTEGER UNSIGNED,
ProductId INTEGER UNSIGNED,
Qty INTEGER,
Price DECIMAL(5,2),
Cost DECIMAL(5,2),
PRIMARY KEY(Id),
FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),
FOREIGN KEY(ProductId) REFERENCES Product(Id)
);
CREATE TABLE PurchaseOrderLineItem (
Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
PurchaseOrderId INTEGER UNSIGNED,
SalesOrderId INTEGER UNSIGNED,
ProductId INTEGER UNSIGNED,
ClientId INTEGER UNSIGNED,
MfgId INTEGER UNSIGNED,
PRIMARY KEY(Id),
FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),
FOREIGN KEY(SalesOrderId) REFERENCES SalesOrder(Id),
FOREIGN KEY(ProductId) REFERENCES SalesOrder(Id),
FOREIGN KEY(ClientId) REFERENCES Client(Id),
FOREIGN KEY(MfgId) REFERENCES Mfg(Id)
);
I'm thinking to create additional association tables between Sales Order Line Item and Purchase Orders and for Purchase Order Line Item and Containers to keep track of this kind of back and froth splitting?
Help appreciated!