Is it efficient to have 2 SQL tables with the same structure?

Question

In inventory / production systems I usually implement a table structure similar to the following description...

--- Raw Item ---
id INT(10) UNSIGNED AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
description VARCHAR(128) NOT NULL,
ideal INT(10) UNSIGNED,
PRIMARY KEY(id)

And another table with the same fields for the processed items...

Next tables for clients and providers with similar structures.

Then tables for income orders and outcome orders with similar structures.

And finally, a table which stablishes relationships between a certain processed item and the types of raw items (With quantities) required to produce a batch...

It performs well... But I wonder if it would be better to merge the similar tables and adding a field such as 'type', would like some advice please.

Answer 1

In my mind adding an artificial type to combine similar data is not 3NF. Go with separate tables unless you need the data in the same table.

A client and a provider have similar fields but they are two different things.

If an order migrates from PO to Processed then it is the same thing and it would be appropriate to have a status flag. If you are moving data from one table to the other then combine with a flag is preferred.

Answer 2

That's a good question and as many SQL good questions the answer is "it depends".

IMHO it's ok to create similar (internal structured tables) for different artifacts (with similar properties). See you can get:

Owner
Id, Name

Pet
Id, Name

Tables with same columns but different meaning. Sure you can got Items and RawItens in the same table and just a Flag column to differentiate between both. You can even use a self referencing FK to relate Items with RamItems but how that can affect performance?

Well as your table grows engine will need more time (resources, mem, cpu) to retrieve rows/data. If you double the rows... for most DBMS doubling tables ill affect performance a lot less doubling a table rows.

Also it affects evolutive maintenance. If now you need to add one column for your RawItems but not for your Items you can become wasting space.

"Merging" similar tables can increase dificult to understand your schema, not simplify it.