2

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

  • A chat room has been created for discussion on this question. Please use that facility rather than adding more comments, thank you. – Paul White Mar 18 '16 at 14:17
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.