CODEWITHSUNDEEP
sqldatabase-designrelational-database
Juan Dela Cruz
Juan Dela Cruz

Reputation: 101

Relational Database design: handling sales receipts

I am making an online market for a learning project. Pardon me for not having a diagram.

I have the tables Seller and Product,which contains data about the seller and products, respectively. A Seller can have multiple products. There is also a Receipt table that stores information regarding purchases made by a customer. This is an important record and must persist. The receipt should be able to have information on the item purchased.

However, products are dynamic, products may be added and removed. But since the Receipt should reference the Product, it means that I should not delete a Product row even if it is no longer on sale.

Is this the right way to do it? Are there any better design pattern I can use?

Upvotes: 1

Views: 2617

Answers (2)

TrevorJ
TrevorJ

Reputation: 554

This isn't a proper answer. I just want to give you the gift of a diagram since you didn't have one! :) QuickDatabaseDiagram

(Disclaimer: QuickDatabaseDiagrams is my project)

Upvotes: 1

nico boey
nico boey

Reputation: 389

Yes, that is the right way to do it. If you set the referential integrity right, the system will not allow you to delete a product or seller if it has receipts. The next thing to do is to use a flag to mark the product or seller as deleted or archived. It could be either a boolean or a date that indicates when it became inactive. Using a 'From' AND a 'To' date to indicate valid time intervals, as Hellmar Becker suggests, is very powerfull, but it opens a whole new can of worms: you can have more than one 'valid' period, so you have to extend your primary key. Modern databases like HANA (from SAP) just don't allow deletes any more, and have inbuilt 'deleted' flags.

Upvotes: 1

Related Questions