Search code examples
mysqldatabasemodelingdatabase-normalization

Database Modelling of users table having 2 tyes of users

I am modeling a database that will hold data relating sales from a shop. I have two different kind of users which have distinct properties.

In my normalization process I created a User1 Table and a User2 Table.

The problem I have is tracking sales. For example if I want to track sales say for a particular kind of user for a product and know the users buying trends for that product, I would have to create two tables, Bread1 and Bread2 for the two types of users.

I came up with a solution but I don't know the performance implications on the long run or if its the best solution at all. The solution is having a unified table User which have the ID's of both users.

If there is any other better solution, I would appreciate it. Thanks.

Solution

  • Better to have just one table for Users and one for Products and so on. You can easily categorize your users or products by making an another table for grouping them.

    UserTypes(UserTypeId PK, ...)

Users(UserId PK, UserTypeId FK, ...)

    Also for properties you mentioned for users or even for products, You can have a list of properties in a table and assign them by a third table to users.

    Properties(PropertyId PK, Name)

UsersProperties(UserPropertyId PK, UserId FK, PropertyId FK, Value)