0

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?

2 Answers 2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.