Table with a lot of attributes

Question

I'm planing to build some database project.

One of the tables have a lot of attributes.

My question is: What is better, to divide the the class into 2 separate tables or put all of them into one table. below is an example

create table User { id, name, surname,... show_name, show_photos, ...)

or

create table User { id, name, surname,... )
create table UserPrivacy {usr_id,  show_name, show_photos, ...)

The performance i suppose is similar due to i can use index.

Answer 1

It's best to put all the attributes in the same table.

If you start storing attribute names in a table, you're storing meta data in your database, which breaks first normal form.

Besides, keeping them all in the same table simplifies your queries.

Would you rather have:

SELECT show_photos FROM User WHERE user_id = 1

Or

SELECT up.show_photos FROM User u
LEFT JOIN UserPrivacy up USING(user_id)
WHERE u.user_id = 1

Joins are okay, but keep them for associating separate entities and 1->N relationships.

There is a limit to the number of columns, and only if you think you might hit that limit would you do anything else.

There are legitimate reasons for storing name value pairs in a separate table, but fear of adding columns isn't one of them. For example, creating a name value table might, in some circumstances, make it easier for you to query a list of attributes. However, most database engines, including PDO in PHP include reflection methods whereby you can easily get a list of columns for a table (attributes for an entity).

Also, please note that your id field on User should be user_id, not just id, unless you're using Ruby, which forces just id. 'user_id' is preferred because with just id, your joins look like this:

ON u.id = up.user_id

Which seems odd, and the preferred way is this:

ON u.user_id = up.user_id

or more simply:

USING(user_id)

Don't be afraid to 'add yet another attribute'. It's normal, and it's okay.

Answer 2

I'd say the 2 separate tables especially if you are using ORM. In most cases its best to have each table correspond to a particular object and have its field or "attributes" be things that are required to describe that object.

You don't need 'show_photos' to describe a User but you do need it to describe UserPrivacy.