How to correctly model data in SQL-based databases that have multiple columns but also have unique columns?

Question

For example, let's say I have a User model. Users have things like logins, passwords, email addresses, avatars, etc. But there are two types of users who will use this site, let parents and enterprises say. I need to store some other information for parents (for example, names of children, internal partner, salaries, etc.), than for enterprises (for example, industry, number of employees, etc.), but also some of them are the same. like logins and passwords.

How to properly structure this in a SQL database? Thank!

- UPDATE -

After digging a little more, I think polymorphic association may make sense. But I don’t know much about them, is there a reason why I would not want to use them, or that an STI is better? They seem to be exactly the same, except that fields specific to parents or enterprises are stored in separate tables, and I think this is what I would like. Is not it?

Answer 1

- . . . - , , .

: STI

, , , STI, . :

, , . , .

, Concrete . , , , . DRY .

Answer 2

, :

• ; , :
• , Users; , ;

, , . , , User as it parent, . , .

, , .

!

Answer 3

Users, , , , , UserID. "" ( "" ) , . Businesses UserID , . . UserID Parents and Businesses ( ) Users.

Answer 4

. , (aka 1:1) join :

select u.name, p.DomesticPartner
from users u
join users_partners p on p.userId = u.id

(1: , ) join , :

select n.value as Name, p.value as DomesticPartner
from users u
join users_columns n on n.name = 'Name' and n.userid = u.id
join users_columns p on p.name = 'DomesticPartner' and p.userid = u.id

, .

, , , . :

• (, )

In your case, I would just add all the columns to the user table. You can force the use of unused columns, must be equal to zero using the restriction check:

CHECK (UserType = 'Parent' OR DomesticPartner IS NULL)

Answer 5

Take a look at this question / answer with a similar problem, it describes supertype / subtype relationships.