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?