Database design for a small CRM/invoicing system

Question

I'm currently developing a small customer relationship and invoice management system for my client. And I have run into some small issues which I would like do discuss.

What is the best practice around orders, customers and products. Should my client be able to delete orders, customers and products?

Currently I have designed my database around the principle of relationships between order, customer and product like this:

Customer
ID
Name
...

Product
ID
Name
Price
...

Order
ID
CustomerID
OrderDate
...

Order Line
ID
OrderID
ProductID

Like this I can connect all the different tables. But what if my client delete a product, what happens when he later open a order he created months ago which had that item in it. It would be gone, since it has been deleted. Same goes for customer.

Should I just disable the products and customers when the delete button is clicked or what is the best practice?

If I lets say diable a product whenever my client decides to delete it, what happens then if he later tries to add a new product with the same product ID as a disabled product, should I just enable that item again?

Please share your wisdom :D

Answer 1

Why would you want to be able to delete orders? I would think that such a system would lock orders so that you know you have a good history. Same goes for customers, why delete them? Perhaps a way to "archive" them, having to set some flag, that way they don't show up on customer lists or something.

As for disabling and then entering a new item with the same product ID - I'm not sure why you'd do that either, each product ID is unique for a reason, even if you discontinue a product, it should keep it's product ID so you have a record. But if you must, then you could put a constraint in the business rules, something to the effect of "If there is no product that is active with this product ID then allow it. If we have a product that is active and it has the same product ID, then throw an error." Thus, you only allow for one active product with that product ID - but honestly, I think this would be confusing, and on the back end you'll want to use a unique id that is unchanging for each product to link between tables.

Answer 2

Instead of "deleting" I would add a boolean column for IsActive. That way you won't loose historical data. For instance, if they are able to delete a customer then they won't be able to look at history regarding that customer and it may make looking at statistical data hard or impossible. For the order table you could have a column that represents something like "current", "canceled", "filled" to accomplish the same thing. That column should be a code to a lookup/codetable.

Answer 3

"If I lets say diable a product whenever my client decides to delete it, what happens then if he later tries to add a new product with the same product ID as a disabled product, should I just enable that item again?"

Depends entirely on your business scenario - what is unique in the way customers maintain it currently? (say manually?) How do they handle when an old product which was earlier discontinued suddenly reappears? (Do they treat it as a new product or start referring to the old product?) I guess there are no right or wrong answers to these questions, it depends on the functionality - it is always advisable to understand the existing processes (minus the software) already followed by the customers and then map them to the software functionality.

For eg. you could always add a 'A product with this code already exists - do you want to use that instead of creating a new one?' kind of a message. Also, the product ids that you use in your tables as foreign keys, and the ones that you use to show the customer, better be different - you dont want to get them mixed up.