CODEWITHSUNDEEP
mysqldatabase
Nick Richman
Nick Richman

Reputation: 35

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!

Upvotes: 2

Views: 11841

Answers (2)

Fastcoder
Fastcoder

Reputation: 11

If you want to design it without inheritance mechanism it is also possible like the following diagram.

A base table for storing users and two table, Company and Customer, with their own ids and one-to-one relation to Users table.

user management database sample

Upvotes: 0

Hamdi Baligh
Hamdi Baligh

Reputation: 892

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...)

Upvotes: 1

Related Questions