1

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?)

stack
  • 10,280
  • 19
  • 65
  • 117
  • It's impossible to tell without knowing what these values represent, what a row represents, and how they relate to each other in the real world. What's the actual data? – David Aldridge Nov 21 '15 at 13:04
  • @DavidAldridge Actual data are several dictionary tables. Table1: "Chinese words", Table2: "French words" and so on ....! In there word all tables have a common column that is containing English word. I mean is all tables are `English to French` or `English to Chinese` – stack Nov 21 '15 at 13:12
  • 1
    A single table ought to be the correct approach, with the other column being a key to the language then. – David Aldridge Nov 21 '15 at 13:15

2 Answers2

2

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        |
+----+-----------+----------+--------------+
Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • 1
    OK, now I get it. If you had called the tables "French", "Spanish", "German" instead of Table1, Table2, Table3, I would have seen it sooner. – Walter Mitty Nov 21 '15 at 19:37
1

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.

Community
  • 1
  • 1
Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62