Database design — companies, employees, individuals

database-design

I'm having troubles modeling this database schema, will appreciate any help. Here is the requirements and description.

  • A user on registration creates his profile.
  • User can be an individual or a company, which is selected on registration.
  • User of type company can have multiple employee profiles.
  • All profiles have some data in common, like name, location, images, phones.
  • Company profiles have additional details like company type.
  • Humans (individuals & employees) share some additional details like age, gender.
  • Businesses (individuals & companies) also share some common details like work schedule, pricing.
  • Most frequent queries would be:

    • showing all profiles (company & individual & employee);
    • filtering profiles by selected types (company and/or individual and/or employee);
    • filtering all profiles by common details; filtering all profiles of selected type (all humans, ind. humans, employees, companies) by the type's details.
  • Profile of any type can be created by an admin, w/o linking it to a user.
  • One user has only one profile.
  • Only company profile can have multiple employee profiles.
  • An employee can't work for multiple companies.
  • An employee can't be the same person as some individual.

I want to normalize this as much as possible and then possibly denormalize something to improve usability and speed.

Database vendor preferably lowest common denominator – MySQL, possibly PostgreSQL, if it suits this task better.

My initial schema (I think it sucks, because it doesn't enforce many of the constraints and badly expresses the domain):

schema

Best Answer

Read up on Table Inheritance.

Here's your data model in pseudo-syntax. : means inherits. Indentation means that thing is a property of the type.

AbstractParty
  BirthDate -- companies have "created" dates too
  Name

Individual : AbstractParty
  Gender

Organization : AbstractParty
  CompanyType

AbstractPartyRelationship
  FromAbstractParty
  ToAbstractParty

Employment : AbstractPartyRelationship

User
  Username
  Password
  Party (make this a one-to-one relationship)

AbstractAddress

PhoneNumber : AbstractAddress

EmailAddress : AbstractAddress

URL : AbstractAddress

MailingAddress : AbstractAddress

AbstractContactInfo
  AbstractParty
  AbstractAddress

PhoneContactInfo : AbstractContactInfo
  Extension

Picture

PartyPicture
  AbstractParty
  Picture

An employee can't work for multiple companies.

That's a business rule to be enforced by a trigger, not your data model

An employee can't be the same person as some individual.

Please elaborate.