CODEWITHSUNDEEP
database-design
ajm
ajm

Reputation: 13213

How to design database schema for Users, Products and sub products?

The system will two types of users. Admins and normal users. Admins can have access to products only. If an admin have access to a product A, it means he also has access to all its sub products.

Normal users can have access to only sub products (one or more).

Screen to add Admin users will have a drop down of products while screen to add users will have a drop down of sub products.

Product Admin will add product sub users.

There is going to be a single table for both Admin users and Normal users as everything else is same for both users.

Please help.

Upvotes: 1

Views: 552

Answers (1)

Yuck
Yuck

Reputation: 50855

You're basically looking at this:

SystemUser: Id, AccountName
SystemRole: Id, RoleName
UserRole: SystemUserId, SystemRoleId
Product: Id, ParentId, Name

Product will have a self-reference on ParentId. If it's not a sub-category then ParentId should be NULL. You'll want anyone who doesn't have a SystemRole of "Admin" defined to be unable to access any Product that has a NULL ParentId.

UserRole is an association table that provides the M:N relationship of SystemUser to SystemRole. Even if each user will have a single role it's a good idea to set up the structure this way for now so your application is easier to expand in the future.

Upvotes: 1

Related Questions