Inclusion of additional requirements in a legacy database design

Question

I struggle with database design, this is what I still have.

Here are the problems.

• I need to introduce a new type of user (conglomeration manager), and they will have the visibility of groups of companies (conglomeration). A conglomeration manager may have several companies, and a company may belong to several conglomerate managers. It would be beneficial if you could add an independent company, and then easily be included in the conglomerate at a later date.

  It’s hard for me to model, since all my users still (manager, driver, recipient) all exist in the user table. This was by design, since they all have almost the same data fields, and I need to have a single entry 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 do not exist with my existing user types.

• I am worried about the dependency cycle formed by users, owners, packages, companies, users. This strikes me as a bad form, but I really can't figure out how to avoid it:

  Managers, drivers and recipients

  work in one company. This company has an associated set of packages, but I need to be able to associate a subset of these packages with a specific recipient (they belong to the packages) and a specific driver or manager (responsible for delivering these packages).

• I am not satisfied with the "receive_emails" field among users, since this applies only to users of the "recipient" type.

To add to the problems, this project is already in use, and the data should be transferred to any new design.

The most common operations that occur in the system are viewing statuses by recipients, and then creating statuses by managers and drivers.

Is it possible to solve problems with an elegant new design?

Answer 1

Ok, here's another try. I still think

• you should not worry too much about this field of emails received, as explained in my other answer.
• You do not need to divide users into user types.

What you worry about in 2 is dependencies. Dependencies are usually not a problem, but you are very strict in your id-based database design, thereby hiding the dependencies on your dbms. If he just knew, this could help you :-)

You can do it:

• Stick to the users table, but delete company_id.
• You do not need to make any changes to "companies", "packages", "participation" and "statuses".
• Add a table to associate users with companies. Let me call the table "affiliations" for now. (I don’t know if this was a suitable name, my English couldn’t get me here.) Like the owners, this is just a link table, so the only fields are user_id and company_id (primary key generation).
• Now add company_id to the "owners". (I know this is implicit because of your link to "packages", but dbms does not know this.) So, add a field and now that your dbms see this field, you can also add a restriction (foreign key) to package_id plus company_id on "packages" and the restriction on user_id plus company_id on "affilations".

What is it. You have not changed much, but now the user can be associated with many companies (conglomerate managers so far, but perhaps one day you will decide to allow recipients to work with several companies or to allow drivers to work with more than one of the companies for a while). And now there is no risk of erroneous entries in the "owners" or any doubt about the content and use.

If you want to play in a safe place with your receive_emails field, you might want to go (as I said, this is not necessary): enter a new email_recipients table with two fields: user_id and user_type. Yes, redundancy again. But at the same time, you can have a restriction on user_type, allowing only certain types (only the "receiver" so far). Again you will have a foreign key not only for user_id, but also for user_id plus user_type.

Answer 2

Expand your users!

Like the class extension, you can create a new “Managers” table with many columns and FK for users.

So, you can create a relational table between managers and companies.

If you want to better control this conglomerate object, create a Conglomerate table and create an FK for managers, so you will create a relational table between Conglomerate and companies OR if the company cannot belong to two conglomerates, only FK from the company for the conglomerate.

Answer 3

I need to enter a new user type

In this type of scenario, adding a new type is required, which will lead to a restructuring of the circuit, leading me to a design flaw.

In fact, control and driving are roles that the user plays, which may change over time.
Actually:
The user is not a manager (he is a person).
Management - user role roles .
Consider whether the company decides to have help desk users.

I will add Role and User-Role tables to maintain the relationship between the user and the role.

I am not happy with the receive_emails field among users.

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

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

Does the user, company and role help in choosing the login page (which will have a direct impact on the design of your application).

I am worried about the dependency cycle formed through users, owners, packages, companies, users.

Conceptually, we will have user-receiver-user => owner => package => company => driver or manager.
BTW is a relational model.

conglomerates.

Answer 4

3: Don't worry about the receive_emails field. Keep in mind that your database is just a model of the real world and should not be perfect. Yes, you can make the "recipients" your own table. Think about what you gain and what you lose. Be that as it may, this is not a bad design. You can use a trigger to set receive_emails to false if the user is not a recipient. Or use the view to hide the field for applications that work with drivers or managers. Just as you like. Well, if you really want to get rid of the field, you can have an emeil_recipients table containing all the user IDs that are the recipients of the emails. You see, there are many ways to solve this problem, and all of them have their advantages and disadvantages. Your design is fine.

2: As I understand it, each package has up to one manager, one driver and one recipient. This is true? Then why do you have a table of "owners"? Place the three fields in the packages table; user_id_driver, user_id_manager, user_id_recipient. Thus, your model is much closer to reality. (You can create the owners view to replace the owners table during the migration.)

1: Now in conglomerates. The easiest way would be to introduce two new tables: first you would have a table "company_groups" with an identifier and, possibly, a description field. Your "users" of the table will have a "company_group_id" field, which will replace the "company_id" field. Thus, you associate users with groups of companies, and not with individual companies. Your second new table will be "company_group_members" with two fields: id_company_group and id_company. You would create “groups” consisting of only one company (for managers, recipients and drivers) and groups consisting of a larger number of companies (conglomerates for conglomerate managers). Thus, your database does not change so much, but offers everything you need.

Having said all this, you can still think about reducing the "users" of the table to common fields and have new tables "managers", "recipients", "drivers" and "conglomeration" that contain additional fields. This brings you closer to reality and improves connectivity with packages. However, this is due to a more different model from your current one. And what if you add co-authors, secretaries, or anything else? Every time a new table for a new job? Again: There are many ways to build your model. Choose the one that suits you.

I hope my advice will help you think through everything.