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