Database design in MySQL

Question

I am designing a database for an web application, in this system there are multiple users. Consider,

1. Public (Buyer).
2. Merchant.
3. Tele Marketing Executive(TME).
4. Marketing Executive(ME).
5. 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.

1. Sale No.
2. TME Name.
3. ME Name.
4. Merchant Name.
5. Total Amount.
6. 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.

FYI:

enter image description here

enter image description here

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.

Answer 1

"The Data Model Resource Book", Volume 1, has a whole chapter on dealing with contacts and solves those kinds quite extensively. As does any CRM btw.

Basically:

• Sorry, the userRole approach sucks - it is widely accepted - by anyone with experience as a mistake. So, your staff NEVER EVER BUY? Nice products. Roles and entity information are separate.

• There are obviously relationships between entities - a is staff of b (from time to time, btw.). This is a m:n relationship and requires a second table.

Btw., I really hope you do not plan to store the password in the password field - beginner mistake, violation if basic security principles. Never ever store a password, only a salted hash - hashed many times. Read up on that topic.

• Rathish Kumar B: Thank you, Tom. I couldn't get the direct answer for my question (due to lack of my understanding about database), but I got a good book, thanks for your suggestion. I hope surely that will help me. Is there any easy to understand resource available for conceptual designing?
• TomTom: No. Some things just take some time to learn. Database design is one of those.