Question :
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.
Answer :
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).