MySql multiple tables or one big table?

Question

I am currently making an agency CMS and would like to ask about user details. So the registration form is split to 2 groups, Photographers and Models. Models can select options about their body type, suc as Hair color, Hair Lenght, Height and other, but these options are not needed for photographers.

My question is, wich is more effective? Storing everything in a big table or spliting the body detalis.

Example

users_table <--- contains everything

or

users_table <--- containing login details and a few basic information
users_body_table <--- containing model body information

If someone could give me some info about this would be happy

thank you folks

Can you envisage a scenario in which a) a model takes a photograph and/or b) a photographer poses for one? — Strawberry, Jul 12 '13 at 11:04
I'd personally go for the second approach - splitting data into two tables. I found it "cleaner" and just simply better :D — paranoid, Jul 12 '13 at 11:04
You should split it up. What if later you have agencies? Then, their specific information would be added to your big `users_table` as well ... Also, splitting this information up is more relational and it makes it easier to modify data about a model (size, etc.) without having to modify the user information (login credentials, ...) — Dominik Sandjaja, Jul 12 '13 at 11:05

score 3 · Accepted Answer · edited Jun 07 '20 at 07:06

From your description I would definitely recommend going with second approach - splitting the data.

You should notice that in the first approach you will have roughly one quarter of the table filled with nulls.

About nulls effect you can see for example here

The most important thing that will determine which is more efficient is how much you will need only get Photographers or only Models. For me it's quite clear that probably often - and because of that, with split table it will be better.

And maybe if you will meet some need of changes, it will be easier to maintain to change it for only one table (if for example some new Model trait will be added).

Think in terms of future normalization

score 1 · Answer 2 · answered Jul 12 '13 at 11:31

With a splitted tables you can easily run through one table if you want just retrieve data on this. If your big table have a lot of data the time of retrieving will be too much. if you map the table to object it is better to have a small object. you can reuse it too.... Maybe you can use view if you want really one table (view is a transparent dynamic table)

score 0 · Answer 3 · answered Jul 12 '13 at 11:37

Good way is separate tables. One table for common details (users) (login, password, etc) Second table for models (users_models) and 3rd table for photographers (users_photo)

In table users you can make 2 fields for store id from models and photo tables. In this case one user can be model or/and photographer.

MySql multiple tables or one big table?

3 Answers3