I am designing a database for an web application, in this system there are multiple users. Consider,
- Public (Buyer).
- Tele Marketing Executive(TME).
- Marketing Executive(ME).
- Lead Generator...etc.
I have decompose the User table into UserType and UserRoles, according to me, this is known method.
The issue is when I trying to design for sales dashboard, I need to get the values of Tele Marketing, Marketing and Merchant.
The Sales board will be consisting of following fields.
- Sale No.
- TME Name.
- ME Name.
- Merchant Name.
- Total Amount.
- Payment Mode...etc.
For sales_dashboard table I need to use the User_id as a Foreign key, but my doubt is can I get the TME Name, ME and Merchant details from User table by using that foreign key.
Also I need to find the Users who belongs to Merchant but same doubt both Users, Merchants and staffs in a same User table, how can I identify the particular staff is belongs to particular Merchant?
Any method to do this, I hope this is my mistake sorry for my lack of knowledge, I am new to Database.
Thanks in advance.