1

I want to store on MySQL two kind of information:

1) Information with fields A, B, C, D

2) Information with fields C, D, E, F

Should I create two tables, one for each type of information or should I create only one table that has fields A, B, C, D, E, F and null records depending if the information entered was type 1 or type 2?

Table_info_1

id
field_A
field_B
field_C
field_D

Table_info_2:

id
field_C
field_D
field_E
field_F

or

Table_info_1_and_2:

id
field_A
field_B
field_C
field_D
field_E
field_F
information_type (1 or 2)
Freddie
  • 115
  • 1
  • 2
  • 14

2 Answers2

0

It depends. If the relation between A, B, C, D and C, D, E, F is not of 1:1, then you have no other option, but defining two separate tables. If you design them into a single table, will you have potentially redundant data? If yes, then it is better to separate them into two tables. If no, then you might decide to put them all into one table, as you do not want to constantly join them. Also, conceptuality is a factor as well. Are A, B and E, F describing the same entity? If so, then they can be in the same table. If not, then you can separate them. Are you using very rarely one of the pairs of (A, B) and (E, F), but very frequently the other pair? If so, then you can separate them. If not, they can stay inside a single table. As you can see, there are many layers, or, if you will, points of view to analyze your question and the results might be contradicting. It all resumes to the evergreen economical question: If we look at the difference between benefit and harm, which decision has a higher value?

Lajos Arpad
  • 45,912
  • 26
  • 82
  • 148
  • Thanks! but I still have a doubt: I have 6 tables (A, B, C, D, E, F) with relation N:M. The user may select data from tables A, B, C, D or data from tables C, D, E, F. With this consideration, is better to store the selected data in one single table or into two separate tables? And if this latest option is better, why? – Freddie May 14 '14 at 02:40
  • In that case you have to ask yourself the same questions to be asked, shown in my answer. – Lajos Arpad May 14 '14 at 10:19
  • Ok, just one more question: According to your answer I have no other option that create 2 tables, because my 6 tables have relation N:M. What I can´t see is what would be the problem of having only 1 table to store the data like this: id, field_a, field_b, field_c, field_d, field_e, field_f, information_type ? – Freddie May 14 '14 at 17:09
  • The problem is that you will have redundancy, that is, the same pairs will be – Lajos Arpad May 15 '14 at 07:23
  • stored multiple times, which might lead to performance problems and inconsistencies, and, eventually, to bugs. Keeping your data in a consistent state and avoiding redundancies are two very simple, but very important concepts in database-planning. In fact your question is very good, other beginners in the areas of databases might learn from it and the answers provided, so I decided to upvote the question. – Lajos Arpad May 15 '14 at 07:26
0

When you decide to have two different table:
Think about the situation when other table called Table_info_User is going to look-up these two tables (a foreign key from Table_info_1 and Table_info_2 inside Table_info_User).
There are two options to design the relationship:

  • Have a foreign key column for each of Table_info_X inside Table_info_User: you will end up with null able foreign keys, in the mean time one of them must be populated, but you will not be able to show the constraint in you design.
  • Have a single column for all Table_info_X inside Table_info_User with no foreign key constraint: you will loos data integrity, using the pattern will have more problems that it shows!

When you decide to have a single table: (calling it Table_info_all)
You will end up with many null able columns in the table, some of them will be conceptually mandatory, the design will not be in normal form(specially 2NF).

The over all problems will be more tangable when number of Table_info tables will be more than 2.

Alternative solution: (inheritance)
You may have a base table called Table_info_base having shared properties of others.
Other Table_info table will be child of the base table and they will have their specific columns.
A zero-or-one relation between Table_info_base and Table_infox will server.
Having a discriminator column in base table will enhance the design.

Table_info_User will have a mandatory foreign key relation to this Table_info_base table.
The design will be in normal form, unnecessary null-able columns will disappear.
enter image description here



Community
  • 1
  • 1
Mohsen Heydari
  • 7,174
  • 4
  • 26
  • 44
  • Actually I have 6 different tables (Table_1 with field A (PK), Table_2 with field B (PK), Table_3 with field C (PK) and so on…) The relation between each table is N:M. The user can select data from table 1, 2, 3, 4 or data from table 3, 4, 5, 6. To store the selected data, you are suggesting to create only one table called Table_info_User? – Freddie May 14 '14 at 13:03
  • No! Table_info_User is just a sample table that needs to be related to table_info_1 ... table_info_N. – Mohsen Heydari May 14 '14 at 14:23
  • Sorry, still not clear for me. If the user can select data from table 1, 2, 3, 4 or data from table 3, 4, 5, 6, where do I store the selected data? – Freddie May 14 '14 at 17:01