4

I have a users table for my company's management system. Right now, the previous developer split everything into separate table.

So you would have:

  • 'users' ( id, email, username, password, first_name, last_name, last_login, etc ) for work related details

  • 'users_info' ( id, user_id, personal_email, address, identification_num, education, etc ) for personal details

  • 'users_files' ( id, user_id, photo, resume, offer_letter, etc ) for any attachments during hiring / registration process

There are lots more users related table ( the total is around 12 ). Now all of these tables has one-to-one relationship so you could just combine all of it into one big 'users' table with lots of columns.

We are currently in the process of migrating the whole app & restructuring the db itself ( normalization, remove redundant column, etc ). So my question is, which one is better in terms of best practice, performance, scalability & maintenance ?

0

2 Answers 2

2

It seems obvious that these all are parts of one logical entity type and so should be modelled together.

When it comes to implementation there may be good reasons for breaking a logical entity into several physical tables. It may be storage or performance optimisation, to give stronger security boundaries, to satisfy requirements that no longer apply, or even, sadly, politics. You may never know.

For my two cents I'd always start with a single table until I found a compelling reason to split it. If you find such a reason be sure to write it down for your successor!

1

Short answer: Never have one-to-one relationship between tables.

Long answer: There are pragmatic cases, extreme cases, etc, for having lots of columns in one table.

You are probably talking about enough rows for performance to be much of an issue, even if there are lots of messy JOINs.

One-to-one is usually "wrong", but there are exceptions.

A possible exception is when one of those 12 tables has no info for a given user -- then that row can be absent. When you JOIN to get the data, use a LEFT JOIN so you can get NULLs for those columns.

If there are lots of TEXT columns, then you can hit a limit on row size. But you rarely need more than 2 tables, not 12.

What if the person leaves the company and then is re-hired? You might need one table the "persons", and at least one other to have each "hire".

There are limits (both physical and 'practical') on how many indexes can be on a table.

A JSON string could collect "miscellany". But don't put 'columns' in it that you need to filter on. Although there are SQL commands for picking apart JSON, it is probably best to leave that up to the app.

For security, you may want certain columns to have extra security, perhaps not even living on the same server.

"User files" should probably be just filenames, with the files, themselves, on disk is a fashion allowing them to be easily delivered by html's <img src=...>. This is almost universally how photos are stored and delivered.

4
  • Thanks for the reply. So if I merge most of these table into a single users table, there really is no issue with it right?
    – aajj
    Nov 10, 2022 at 7:26
  • @aajj - Right. I listed a few unlikely drawbacks.
    – Rick James
    Nov 10, 2022 at 17:10
  • Well … yes there is a major issue: all the applications that use the current multi-table design will need modifying. And re-testing before being introduced in production. And of course the data itself needs to updated: those 12 tables need to be merged into a single one. That will be tricky in a 24x7 environment and will translate ultimately into a cost and risks. Are those worth the benefits of having a single table ? What are those benefits anyway ? Did you measure them ? Nov 11, 2022 at 7:26
  • @AlbertGodfrind - Sure, changing this late in the game could be a big hassle. As for performance -- turn on the slowlog with a low value of long_query_time. If JOINs among those tables show up too much, then there is probably a performance problem that needs them to be combined. (Or some other simple change could fix the speed.)
    – Rick James
    Nov 11, 2022 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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