7

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!

4
  • 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? May 16, 2013 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.
    – User007
    May 16, 2013 at 16:25
  • A sales order line item requires a minimum of four shipping containers? Is that right? May 16, 2013 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.
    – User007
    May 16, 2013 at 17:26

2 Answers 2

1

In general, be parsimonious with auto-generated keys. If you need one for the user's convenience, OK, but you should still enforce the natural key.

For example, you have PurchaseOrder. It has a unique key, externally provided. use it. Adding your own auto-generated value adds complexity and nothing else.

Similarly, SalesOrderLineItem.Id is unique throughout the table. Wouldn't you rather see line 1, 2, 3 than 62783444, 62783445, 62783446? That makes the primary key SaleOrderID, LineItem (instead of Id). Same for PurchaseOrderLineItem, which then gains a key of PurchaseOrder, LineItem.

You mention "splitting" a few times; I take it you mean a given Sales quantity may be apportioned to more than one Purchase order and Shipping order. Resist the temptation (if it comes) to use a trigger to enforce the relationship that, say, the sum total shipped is not more than was ordered. That kind of question can be implemented in SQL, and should be, but not as a referential-integrity constraint. Write a stored procedure to verify the order is "in bounds", and call it from your application at the appropriate time. That will permit real-world anomalous situations such as lost shipments and botched orders.

Other than that, you seem to be shooting for 3NF, which is a good place to be. If you keep following the 1:n relationships to define tables and enforce constraints rigorously, you'll likely end up with a sound design.

0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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