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

  • there is unique user id for each customer [ i guess we call as primary key ] – Baby in Database Sep 13 '16 at 6:11
  • We are providing an option for registered users to select checkbox for not to receieve emails from site. is we need to create separate column to store the values like - "checkbox is checked" or "checkbox is unchecked" – Baby in Database Sep 13 '16 at 7:09

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.

|improve this answer|||||

Each way has its pros and cons of course. However, here is how I would precede:

I would split the columns into two tables. One for the columns that are frequently used, and the other for the columns that are used less frequently.

For example, if you use this table to authentications users, you probably would need these fields very often: email/username, password, first and last name (for greetings), and probably few other fields, like score or last login time.

On the other hand, you will rarely be in need of the user's address, or the primary school they attended. So, there is no need to load this data each time you use the table.

So, grouping columns into [frequently used, less frequently used] has an advantage of utilizing resources. However, it is slightly more work at table management level.

If you go with the option of having all columns in one table, consider covering indexes for the fields that are usually queries together.


|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.