Design database with 2 simillar tables

application-designdatabase-designrelational-theory

I have a database which has product_sell and product_buy tables, these two tables are different in a few fields and each one has own comment table

product_sell >---< product_sell_cmt
product_buy >---< product_buy_cmt

now , I want to ask which modelling approach is better ?

  • design four tables as showed above

  • design product_sell and product_buy separately with common comment table

  • merge two product tables in one table with unused columns in rows or even using 1 to 1 relations

since, I will use product_sell and product_buy and their comments lonely in some cases , is it useful for indexing and performance to have 4 separated tables ?

Best Answer

Read a plain language description of your data. A Product is a noun. A noun can become an entity and an entity becomes a table (or several tables). The words buy and sell are verbs, actions that take place with entities. Verbs do not become tables. A suggestion was to have a transaction table. Fine, "transaction" is a noun and the actions buy and sell would be one of its attributes.

There are many ways to design such a schema based on your specific needs, but in general you would have one Products table with a complement of supporting tables. One such supporting table could be Comments. A comment is an attribute of the Product entity but you could normalize it to a separate table if you really wanted to. You should have a valid reason for doing so, however. For example, if you want to allow a comment on every state change, that will be several comments possible for each product. First normal form. That's a valid reason. But beware. Such comments would be attributes of the state change (transaction) rather than the entity itself.

Other supporting tables would help you track state changes to your entities. One such would be Transactions which could track "Ordered", "Received/Entered to Inventory", "Sold", "Shipped" and any other state you want to track. While a state is an attribute of the Product entity, there will be several of them (even if only one is current at any given time) so that would require normalization to a separate table.

Many books have been written on database design. But for your example the biggest no-no right now is creating a table to represent actions: buy and sell in your instance. That is going to be really difficult to work with.