MySQL – Save 14 Columns in One Table or Different Tables

MySQL

We are giving option to customer to enter his name, email, telephone like this in 14 textfields he will enter information. for all customers we wil create userid as one column in table.

so in mysql db, is it better to store all these information in one table or in different tables.

if we store in one table , later can we save same information in different tables and viceversa ?

enter image description here

Best Answer

Two tables that are in 1:1 relationship should, in almost all cases, be combined into a single table. Here are some exceptions of where it may be beneficial to have multiple, parallel, vertically partitioned, tables. Note: you probably do not have any of these cases.

  • MyISAM table with lots of rows and one or more big TEXT or BLOB. The layout of MyISAM leads to clumsy access for queries that don't need the bulky columns. (This does not apply for InnoDB.) Even so, there is a workaround ("lazy eval").

  • You have a very rapidly changing column, such as "Likes". Separate it into a separate table (plus an id for JOINing) so that the frequent UPDATEs are not competing with the more mundane operations.

  • Optional columns. If many of the rows do not have a certain column(s), move them to another table and use LEFT JOIN to reconstruct. This is almost not worth mentioning, since NULL is likely to be better for an optional column.

  • A new column being added to a huge table. If you already have a table with millions of rows, and it is very actively used, and you don't want the downtime to add a new column, put it in another table.

Related Question