Best practices when designing SQL DB with "redundant" tables

Question

I have a design dilemma for a DB I'm creating for an e-commerce platform I want to develop.

I have 3 different actors interacting with the website:

• Customer
• Manager
• Supplier

Those 3 actors have the same table structure: email, username, address...

My initial design for the DB was to create a single table (StoreUser), with an additional field to distinguish between the 3 different types of actors.

The issue I see with this design is that when referencing a Customer in the "Order" table for instance, to assign an Order to a Customer, it would be technically possible to assign a "Manager" or a "Supplier" to the order, even though it isn't wanted. Same thing for the "Product" table, to which a Supplier's foreign key should be provided, the "StoreUser" FK would not distinguish between the 3 actors.

On the other hand, creating 3 tables, containing the exact same datafields, seems really redundant, especially from the code perspective (I'm using Django for the website, and I really don't like the idea of having 3 different classes with the same structure.

Which one seems the most logical to you? What's the good practice here?

Answer 1

Customer, Manager, and Supplier to me don't seem like types of users. They are business entities that represent certain roles in a system. Those roles are going to require entities with different attributes. For example, a customer may have a collections flag, while a manager might belong to a department, and a supplier has an accounts payable. So they are different entities and would probably get different tables.

Meanwhile, you have this idea of a person out there in the world who has a name and a means of being contacted. This is a separate entity as well, perhaps called User.

Finally, there is some kind of relationship between these entities and users. If a user can serve multiple roles with multiple entities, or an entity in a particular role can be supported by multiple users (e.g. perhaps a Supplier has multiple employees you can contact), you may need a junction table. But if it is a 1 : 1 relationship then you could add a UserID column directly to the Supplier, Customer, and Manager tables as a foreign key.

Answer 2

A customer is not a supplier and both aren’t managers. Making an abstraction (UserStore) for things that are different, but share some similar properties, is asking for trouble. Sooner or later you’ll get a requirement to add a property that’s only relevant (and required) for one of the three, then what?

On a class level in code it’s even worse, because now you’re not only dealing with data, but also with behavior. You’ll likely end up with many type checks (if it’s a manager, allow this).

In short: Separate database tables, separate classes, avoid behavior duplication using composition, don’t worry about duplicate properties.