Search code examples
mysqldatabase

MySQL database design "user" table

I have a following table in my database. I got a requirement that my USER can be a company or a private user. They have some different specific fields (e.g. in company: company name, person for contact). I am not sure how to achieve this in my database design. Do i have to create 2 separate tables (as it is suggested by normalization rules)? I would appreciate any suggestion!
My USER table:

user_id INT <- PK
first_name VARCHAR(35)
last_name VARCHAR(35)
email VARCHAR(254)
password VARCHAR(45)
birthday DATE
creation_date TIMESTAMP
last_access_date TIMESTAMP
updated_at TIMESTAMP
enabled BOOLEAN

Thanks in advance!

Solution

  • You need to design a inheritance mecanism within your database. One of the methods, and the most clean one, is to design three tables:

    • A parent User table: This table will contain the common fields between company and private users. For example if both company and private users has login and passwords, you user table design will be as follows

    user(user_id, password...)

    with user_id as a primary key (You can chose an other primary key based on your needs)

    • A company_user Table: This will contain the user fields which are specific to company users (other than logins and passwords). This table will have a reference to the login PK of the parent User table
    company_user(#user_id, company_address...)
    • A private_user table This will contain the user fields which are specific to private users (other than logins and passwords). This table will have a reference to the login PK of the parent User table

    private_user(#user_id, home_address...)