I'm writing a stock manager with these tables:
- 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
purchase, because they have almost the same fields but
producer are totally different.
I thought of two options. The first is duplicate fields in
sale_item and in
purchase_item. The second is to create a table named
order and one column to store a type (
purchase), do the same with
purchase_item, and create a table named
person with other tables named
producer that inherit
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.