Table with many attributes

Question

I plan to create a database project.

One of the tables has many attributes.

My question is: which is better, split the class into two separate tables or put them in 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, ...)

Performance, I suppose, is similar because I can use an 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 save the metadata in your database, which interrupts the first normal form.

In addition, storing them in a single table simplifies your queries.

Would you prefer:

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

, 1- > N.

, , , - .

, . , , , . , PDO PHP, , ( ).

, user_id, id, Ruby, id. "user_id" , id, :

ON u.id = up.user_id

, :

ON u.user_id = up.user_id

:

USING(user_id)

" ". , .

Answer 2

, , ORM. , "" - , .

"show_photos" , UserPrivacy.

Answer 3

, NULL , , NULL.

.

, , JOIN.

Answer 4

, , " ", , , , :

, , - .

, .

Answer 5

( identifiable or dependent primary key) ( definite/fixed repeatedly) , .

Answer 6

- differnet. , " " . :

TABLE Attribute
(
    ID
    Name
)
TABLE User
(
    ID
    ...
)
TABLE UserAttributes
(
    UserID FK Users.ID
    Attribute FK Attributes.ID
    Value...
)

. .

, , - . "" NVP - " ?" , . , "". , , , NULL , , .

Answer 7

User table Feature, :

create table User ( id int primary key, name varchar(255) ... )

create table Features ( user_id int, feature varchar(50), enabled bit, primary key (user_id, feature) )

Then the data in the table of your functions will look like this:

| user_id | feature | enabled
 | -------------------------------
 | 291 | show_photos | 1
 | -------------------------------
 | 291 | show_name | 1
 | -------------------------------
 | 292 | show_photos | 0
 | -------------------------------
 | 293 | show_name | 0