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

Well here is another try. I still think

• you shouldn't worry too much about that receive_emails field, as explained in my other answer.
• you don't have to split users into user kinds.

What you are worried about in 2 is the dependencies. Dependencies are usually not a problem, but you are very strict in your id based database design, thus hiding the dependencies from your dbms. If it just knew, it could help you :-)

You could do this:

• Stick to your table "users", but remove the company_id.
• You don't have to make any changes to "companies", "packages", "participations" and "statuses".
• Add a table to link users to companies. Let's call the table "affiliations" for the moment. (I don't know if this would be an appropriate name, my English fails me here.) Like ownerships this is just a link table, so the only fields are user_id and company_id (forming the primary key).
• Now add company_id to "ownerships". (I know, it is kind of there implicitly because of your link to "packages", but the dbms doesn't know that.) So add the field and now that your dbms sees the field, you can also add a constraint (foreign key) on package_id plus company_id to "packages" and a constraint on user_id plus company_id to "affilations".

That's it. You haven't changed much, but now a user can be affiliated to many companies (conglomeration managers so far, but maybe you decide one day to allow recipients to work with multiple companies or let drivers work for more than one of the companies at a time). And there is no risk of wrong entries now in "ownerships" or any doubt aboout ist content and use.

If you want to play safe with your receive_emails field, here is a way you might want to go (as I said, it is not really necessary): Have a new table email_recipients with two fields: user_id and user_type. Yes, redundancy again. But doing this, you can have a constraint on user_type only allowing certain types (only "recipient" so far). Again you would have a foreign key not only to user_id, but to user_id plus user_type.

Answer 2

I need to introduce a new user type

In these type of scenario when adding a new type is required, that would result in restructuring of schema, leads me to a defect in design.

Actually managing and driving are roles played by a user that may change over time.
In reality:
A user is-not-a manager (he is a person).
Managing is-a-role-played-by a user.
Think about if the company decides to have help-desk users.

I will add Role and User-Role tables to keep the relation between user and role.

I am not happy with the "receive_emails" field in users.

Having receive-email field in User-Role will be an option.

I need to have a single login point for all users on my site

May having user, company and role as selections on log-in page helps (that will have direct impacts on your application design).

I am uneasy about the dependency loop formed through users, ownerships, packages, companies, users.

Conceptually we will have recipient-user => ownership => package => company => driver or manager user.
BTW its a relational model.

Conglomerations.

Answer 3

3: Don't worry too much about the receive_emails field. Keep in mind that your database is just a model of the real world and doesn't have to be perfect. Yes, you could make "recipients" a table of its own. Consider what you would gain and what you would lose. As it is, it is no bad design. You may use a trigger to set receive_emails to false in case the user is not a recipient. Or use a view in order to hide the field for apps dealing with drivers or managers. Just as you like. Well, if you really want to get rid of the field, you could have a table "emeil_recipients" holding all user ids who are recipients of e-mails. You see there are many ways to address this, and they all have their own advantages and disadvantages. Your design as it is is fine.

2: As far as I understand it, every package has up to one manager, one driver and one recipient. Is that so? Then why have a table "ownerships" at all? Put three fields in your "packages" table; user_id_driver, user_id_manager, user_id_recipient. So your model is much closer to reality. (you can create a view "ownerships" to replace the table "ownerships" during migration time.)

1: Now to the conglomerations. Easiest would be to introduce two new tables: First you would have a table "company_groups" with an id and maybe a description field. Your table "users" would have a field "company_group_id" which would replace the field "company_id". Thus you link users to company groups rather than to single companies. Your second new table would be "company_group_members" with just two fields, id_company_group and id_company. You would build "groups" consisting only of one single company (for the managers, recipients and drivers) and groups consisting of more companies (conglomerations for the conglomerations managers). So your database doesn't change that much, but offers all you need.

Having said all that, you could still think about reducing your table "users" to the common fields and have new tables "managers", "recipients", "drivers" and "conglomeration_managers" holding additional fields. This gets you closer to reality and makes the link to packages clearer. However, it comes at the cost of a more different model from your current one. And what if you add co-drivers, secretaries or whatever later? Every time a new table for a new job? Again: There are many ways to build your model. Choose the one that suits you best.

I hope my advice helps you think it all through.

Answer 4

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.