Database table design question

database-design

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!

Best Answer

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.

Related Question