What is the best database structure for this situation?


I'm writing a stock manager with these tables:

  • harvest
  • sale and purchase
  • customer and producer
  • sale_item and purchase_item
  • and others

I would like to know what the best way to organize the tables.

My main question is how create the sales and purchase, because they have almost the same fields but customer and producer are totally different.

I thought of two options. The first is duplicate fields in sale, in purchase, in sale_item and in purchase_item. The second is to create a table named order and one column to store a type (sale or purchase), do the same with sale_item and purchase_item, and create a table named person with other tables named customer and producer that inherit person.

What the best approach?

I am using Django with Postgresql.

You should probably separate sale and purchase into two separate tables (and sale_item and purchase_item.) Even though they have duplicate columns -- for now -- they represent separate concepts.

In the future, it's easy to imagine that either of those concepts could change in some way so that the underlying tables would not have all the same columns.

Having two separate sets of tables also makes it easier to query.