Keeping one table or multiple table for similar type of data which one is best while considering high performance

Question

I am designing DATABASE for a Sales and Purchase application like ERP and using MYSQL as RDBMS, I have doubt on creating table for sales and purchase entities to go with single table for each module(Sale/Purchase) or multiple tables for each entities(Sales order, Sale invoice, Sale return, Purchase order, Purchase invoice, Purchase return) in longer run. Below is my use case.

My application will have Sale Order, Sale Delivery, Sale Invoice, Sale Return and Credit Note and same entity for Purchase module also and all these entity may be enter linked in there module. Like Sale Order can be converted into Sale Delivery or Sale Order and Sale Delivery can be converted into Sale Invoice. So there need to maintain reference b/w each entity of a module.

Now, I am little confuse to keep all this in one table for each module say "sale_entity" and "purchase_entity" having entity type Or should I create separate table for each entity type say sale_order, sale_invoice, sale_return, purchase_order, purchase_invoice, purchase_return etc.

Below is what running in my mind for both the cases:

Single Table: I really want to keep this in single table for each module but I am worry about performance in longer run, It will increase the table size quickly and may slow down the performance.

Multiple table: It will be difficult to manage, maintain relationships and fetching data in reports for all entity types of records at once, requires union and all.

My understanding is that large size of table performs slower than the small size of table, Please correct if I am wrong.

Please put some light on it, and suggest me how should I proceed.

Thank You

Answer 1

Rule of thumb: If two tables have identical (or nearly identical) columns, make it one table, not two. You may need to add a column to distinguish the two types of data. You may need to have a column be NULL if it applies to one user but not the other. Too many NULLable columns --> don't combine the tables.

Rule of thumb: One-to-many and many-to-many relationships require having two or three tables. (The third is for many-to-many.)

"Orders" usually involved "Order Items". That is one row in Orders maps to one or more OrderItems. Those should be separate tables.

"Purchases" versus "Returns"? Maybe they could be in the same table, and be distinguished by the sign of the amount?