Incorporate additional requirements into a legacy database design

Question

I am struggling with a database design, this is what I have so far.

Here are the problems.

1. I need to introduce a new user type (conglomeration manager), and they will have visibility of groups of companies (a conglomeration). A conglomeration manager can have multiple companies, and a company can belong to multiple conglomeration managers. It would be advantageous if an independent company could be added, and then at a later date be easily included as part of a conglomeration.

   I am finding this difficult to model as all my users so far (manager,driver,recipient) all exist in the users table. This was by design as they all have nearly the same data fields, and I need to have a single login point for all users on my site. If I add a conglomeration manager to the users table, they will have relationships with other tables that my existing user types don't have.

2. I am uneasy about the dependency loop formed through users, ownerships, packages, companies, users. This strikes me as bad form, but I really can't think of a way to avoid it as:

   managers, drivers and recipients all work for a single company. That company has an associated set of packages, yet I need to have the ability to associate a subset of those packages to a particular recipient (they own packages) and to a particular driver or manager (responsible for delivering those packages).

3. I am not happy with the "receive_emails" field in users, as it is only relevant to users of type "recipient".

To add to the the problems, this design is already in use, and data will have to be migrated to any new design.

The most common operations that take place in the system are the viewing of statuses by recipients, followed by the creation of statuses by managers and drivers.

Can my problems be addressed with an elegant new design?

Answer 1

Extend users!

Like extending a class you can create a new table "Managers" with more columns and a FK to users.

So you can create a relational table between Managers and companies.

If you want a better control over that conglomerate entity, create the Conglomerate table and make a FK to managers, so you create a relational table between Conglomerate and Companies OR if a company cannot be owned by two conglomerates just a FK from company to conglomerate.