Is it efficient to have two SQL tables with the same structure?
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 processed items ...
The following tables are for customers and suppliers with similar structures.
Then tables for revenue orders and original orders with similar structures.
And finally, a table that establishes the relationship between a specific processed item and the types of raw items (with quantities) needed to create a batch ...
This works well ... But I'm wondering if it would be better to combine similar tables and add a field such as "type", would like some advice.
source to share
In my mind, adding an artificial type to combine data like this is not 3NF. Go with separate tables if you don't need data in a single table.
Customer and supplier have similar fields, but they are two different things.
If the order is transferred from PO to processed, then it is the same and it would be appropriate to have a status flag. If you are moving data from one table to another then a flag is preferable.
source to share
Good question and how many good SQL questions the answer "depends".
IMHO, it's okay to create similar (internal structured tables) for different artifacts (with similar properties). See, you can get:
Owner
Id, Name
Pet
Id, Name
Tables with the same columns but different values . Of course, you can get Items and RawItens in the same table and only the Flag column to distinguish between both. You can even use a self-referencing FK to link to items using RamItems, but how might this affect performance?
Well, as your table grows engine, it will take more time (resources, memory, cpu) to fetch rows / data. If you are doubling rows ... for most doubling tables the DBMS is bad for performance, much less doubling table rows.
It also affects evolutionary maintenance. If you now need to add a single column for your RawItems but not your items, you might be wasting space.
"Merging" tables like this can increase the dificult to understand your schema rather than simplify it.
source to share
I would guess it will depend on the database schema you choose. See this link for more information; https://dev.mysql.com/doc/refman/5.1/en/storage-engines.html
Each will offer you varying degrees of performance improvement and lack of features in certain areas. I guess the real question is, do you want to deal with multiple tables, or would it be easier to deal with a single table?
A simple solution for this would be to add something as simple as a status to the table. Then just change your queries to trigger a status check. A very simple and efficient way to save both tables into one.
--- Raw Item ---
id INT(10) UNSIGNED AUTO_INCREMENT,
name VARCHAR(32) NOT NULL,
description VARCHAR(128) NOT NULL,
ideal INT(10) UNSIGNED,
status VARCHAR(9) NOT NULL,
PRIMARY KEY(id)
SELECT * from items WHERE status="PROCESSED";
Personally, I would prefer this approach. This leaves you with only one inventory table, as opposed to having multiple tables cluttering your database schema. Not to mention if it ever expands (let's say you have new ones, processed and archived).
source to share
How are you going to use the data over time? If you need to concatenate all of these tables together for reporting, one table is probably preferable with partitioning if it is very large. If you need to move records from one table to another as it moves a process, it is easier to check the process to check if it's all in the same table.
Also, if things are very different, they can have different related tables, and then combining them just mixes the waters, makes this database more difficult to understand, and reduces the efficiency of your PK / FK relationship. In this case, split tables make the most sense. It also makes the most sense if you think data will drift over time as planned features are added.
Take a customer and a sales rep, for example. They can have many of the same fields, but what they refer to will be very different and you don't want the client to be put into a child table for reputation. So now you have to strike up a relationship with more than FK. Also, if you have enough child tables, it makes it difficult to delete records. Db should check all tables, even if only half of them can apply to a particular record.
In one database I saw, the original designer combined two things that were dissimlar but had the same margins on the parent and ended up with over 100 FK on this table. It was a nightmare to be removed from this table and never fast. And there were occasional data integrity issues where one record type ended up in the wrong child tables.
source to share