Small Data/Class-Structure Design Problem...

[steverbs]

Hi all.

I'm working on the order-processing section of an e-commerce system and have come accross a small issue in the data-structure and I am hoping someone here can help.

When someone adds items to their basket, a SalesOrder is created, which contains a collection of SalesOrderItems, which hold a reference to their respective Product. This is all fine and well, but what happens once the order is completed and a few months later, the referenced Product(s) are deleted from the system? When the customer or admin go back to look at the order, it won't show properly.

Now, I know what options I have for getting around this; that's not the issue. But, as this (Order Processing) is a common feature of soooo much software, I would like to know what the usual (tried-and-tested, if you will) approach to storing orders is. Do I create an Invoice Class, or do I replicate the important Product data (name, code, price, options, etc) within the Order-Classes?

Any help is greatly appreciated.

Regards.

Stephen.

 

[Craig0201]

I'd go for a Line class which is inherited by both InvoiceLine and OrderLine classes.

Also, I wouldn't delete a product in the database. I'd have a column for ExpiryDate in the Product table. Then an item can be expired by setting the date but the details of that items are maintained to keep integrity.

Craig

 

[steverbs]

Yeah, it was the data-integrity that I was worried about. I think I'll go for a modified version of your expiry solution and add a check-box for 'dis-continued'.

Cheers for the help.

Steve.

 

[stevexff]

Suggest you keep all the products, as Craig says. Either have a boolean isCurrent column or start and end date columns on the product table. This allows you to restrict the display to current products for selection on new purchases, while still being able to show old purchases for discontinued products.

You can do the same thing for product prices. Put them on a separate table, with unique ids, a foreign key on product ID and with a start and end date column. This way, if you prices change over time, historical invoices won't be affected.

 

[dragonwell]

Just like to add - if you do end up using a simple bit value to indicate active/inactive items, keep the name of it as a poistive value. i.e. - "IsCurrent" or "Active", NOT "Deleted" or "Discontinued".

This will make coding a lot easier when you are testing for these values. It makes more sense to write statements like

If myProduct.IsCurrent Then...

whereas

If Not MyProduct.Discontinued Then...

is just a bit more awkward.

Good luck.

Greetings,
Dragonwell