CODEWITHSUNDEEP
mysqldatabase-designdata-structures
stack
stack

Reputation: 10218

Several separated tables vs one integrated table with an additional column?

I have 3 tables which all of them have the same structure:

// table1              // table2             // table3
+----+------+          +----+------+         +----+------+
| id | name |          | id | name |         | id | name |
+----+------+          +----+------+         +----+------+
| 1  | jack |          | 1  | ali  |         | 1  | peter|
+----+------+          +----+------+         +----+------+

Well, I want to know, my current structure is better or an integrated table along with one additional column? something like this:

+----+------+-------+
| id | name | which |
+----+------+-------+
| 1  | jack | table1|
| 2  | ali  | table2|
| 3  | peter| table3|
+----+------+-------+

Note: It should be noted that in the current structure (several tables) my query is something like this:

select id, name from table1
    union all
select id, name from table2
    union all
select id, name from table3

Now I want to know converting those several tables to one table and add a new column is better or not? (I think that new column is kinda overload, is it true?)

Upvotes: 1

Views: 72

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

This has practical consequences and also philosophical consequences. From a practical point of view, it's very hard to know without knowing a lot more about how the data is going to be used. what's the read to write ratio for this data? How often is data from two or more tables going to be selected in a single query? If you have to do a UNION to get all the data gathered, it's both slower and more cumbersome.

I prefer the philosophical approach, starting with the subject matter. Is there only one kind of entity here, or are there three different entitites that all happen to have the same attribute? That nearly always tells me whether to put them in the same table or not, and also turns out to give the right answer to the practical issue as well, most of the time.

I will say that I would be looking around for some better name for the values of the extra attribute. "table1", "table2" and "table3" seem terribly opaque to me. The subject matter should provide a clue here as well.

Edit:

now that I get the subject matter, I'm going to opine in favor of a single table. It is an opinion rather than a hard and fast rule. So it would be something like.

+----+-----------+----------+--------------+
| id | word      | language |translation   |
+----+-----------+----------+--------------+
| 1  | butterfly | Spanish  | mariposa     |
| 2  | butterfly | French   | papillon     |
| 3  | butterfly | Italian  | farfalla     |
| 4  | chair     | Spanish  | silla        |
+----+-----------+----------+--------------+

Upvotes: 2

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

If you are sure that all three tables will remain have common attributes then the option of single table is fine and if that may not persist then don't think about it.

This thread may help you more.

Upvotes: 1

Related Questions