Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
1  
Are you just trying to audit what has happened or do you need to show which lines were assigned to a particular PO/container at a given point in time? –  Chris Saxon May 16 '13 at 11:49
1  
Need to know which lines are assigned to which POs and then to which Containers. The problem is a SO line of a product A of 100 qty might be split into 2 POs of 50 qty each and then put in 4 containers of 25 qty each. To further compound the problem this process will be reverted multiple times to original state of 100 qty and then redistributed among POs, Containers in a different manner. –  Reduce to Max May 16 '13 at 16:25
    
A sales order line item requires a minimum of four shipping containers? Is that right? –  Mike Sherrill 'Cat Recall' May 16 '13 at 16:55
    
Nope, minimum is 1 container, but the number of containers is a variable. The problem I can't wrap my head around right now is once a sales order line item is allocated into x number of POs it can and will change to y number of POs (and will likely change to z number of POs...). Same with a PO line item when allocated into x containers. We have a messy operation that requires a lot of flexibility. –  Reduce to Max May 16 '13 at 17:26

1 Answer 1

CREATE TABLE PurchaseOrderLineItem (
    Id INTEGER UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT,
    PurchaseOrderId INTEGER UNSIGNED,
    SalesOrderLineItemId INTEGER UNSIGNED,
    SubQty INTEGER UNSIGNED,
    ContainerID INTEGER UNSIGNED,
    ClientId INTEGER UNSIGNED,
    MfgId INTEGER UNSIGNED,

    PRIMARY KEY(Id),
    FOREIGN KEY(PurchaseOrderId) REFERENCES PurchaseOrder(Id),
    FOREIGN KEY(SalesOrderLineItemId) REFERENCES SalesOrderLineItem(Id),
    FOREIGN KEY(ContainerID) REFERENCES Container(Id),
    FOREIGN KEY(ClientId) REFERENCES Client(Id),
    FOREIGN KEY(MfgId) REFERENCES Mfg(Id)
);

Linking to the SalesOrderLineItem would allow you to have multiple purchase order line items in different containers for any sales order line item.

SubQty could, for instance, be 25 in four PurchaseOrderLineItems and the total quantity of 100 in the SalesOrderLineItem.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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