2

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!

Improve this question
3
  • You need 2 different tables! One of the options is to have 3-rd table with "user" data from PC viewpoint - store user_id, display_name (compound person or company name), password, last_access, enabled... 2 columns with ID-s will refer to Persons and Companies tables. Commented Dec 18, 2014 at 15:15
  • 1
    It's up to you but, if it was me, I would insist that ALL individuals belonged to an 'organization' of some sort - although in some instances that might be a 'pseudo-organization' like 'project x client body'. I hope nobody has a 254 character email address. And plain-text passwords aren't a great idea. Commented Dec 18, 2014 at 15:18
  • Probably 3 or more tables to get useful schema - account, company, user. Hard to say without knowing more about the application and what a user account means. As such, this is quite a broad question. Commented Dec 18, 2014 at 15:19

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.