Database design -- companies, employees, individuals

Question

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

Answer 1

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.