There is a user table which have many fields actually around 120-150. All these have only user_id as the primary key. What is the best approach for the DB design. To have a single table with all these fields or spiting up to many tables. One of my main concern is if I split it into many tables will it affect the performance since it require many JOIN queries to display the data.

  • 2
    It is good to have multiple tables in that case. Suppose you have to store user basic information, academic information, contact information. you can have multiple tables having UserId as primary key. `` – mmushtaq Sep 3 '16 at 6:33
  • I think you can't define UserId as primary key in secondary tables because you can have multiple contact, acedemic, basic information of single user but you can make it foreign key with the reference of main user table and add one extra column to define the primary key. You can create table as tbl_user(UserId, name, address), tbl_eduction(EducationId, UserId, ....., – Susang Sep 3 '16 at 6:42
  • @mmushtaq , my concern is will it affect performance since we need more JOINS for displaying the data. – Krishnadas PC Sep 3 '16 at 10:44
  • 1
    @KrishnadasPC You should as well consider the overall undermined retrieval speed and the logical implications of such a wide table, in the case of the one with 120-150 columns. – MDCCL Sep 3 '16 at 15:28
  • 1
    Do you know something of data normalization and normal forms? – Mladen Uzelac Sep 3 '16 at 18:27

If you have 100-150 columns, it is likely some of those are repeating groups. Those should be normalized into their own child tables (PK: user_id, sqn). Review your design. You should normalize to at least 3rd normal form.

Joins are usually very efficient, and in many cases only rows required for the result are joined. With that many columns, it is unlikely you would ever have to return all the columns, so you would likely be joining a subset of the tables.

You should be concerned with the cost of searching for matches on columns that aren't indexed. The higher the column number, the more expensive it is to find the column to compare it. This cost is likely to far outweigh the cost of joining.

The main reasons I use a one-to-one tables are:

  • Separate large tombstone data from volatile information.
  • Separate data with higher security requirements, from more public data.
  • Store sub-type data (one to zero-or-one).

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.