Database Design model a Customer, Cook, User relationship

Question

I am very confused and I have read a lot on internet & SO but I cant figure out. In this design, scalability is an important task for me.

I am making a portal where people can offer to sell their food or can go to this portal to buy food.

I am thinking about following 3 options:

1. Make 3 tables. User, Customer, Cook.
2. Make 2 tables. User, Customer.
3. Make 2 tables. Customer. Cook

I created a User table mostly for security because I dont want secret data to reside in Customer or Cook table.

Problem is that a Cook can also be a customer.

I am thinking of approach 1. How do I model it. Do I use recursive approach. Recursive approach works with Employee and Manager idea (standard textbook example) but it seems strange here.

In approach 2, I use 1 table and I do not differentiate between cook and customer. Not making this distinct, seems like a bad idea somehow.

Approach 3 is possible but I cant think in my mind, how it can work

A user table is like this:

A customer table is like this:

Answer 1

I recommend using the three table approach to separate data specific to each role.

• The User table contains data that is common to both roles, such as the user's name.
• The other two tables hold only role-specific data.

The relationships between User and Customer (or User and Cook) are one-to-zero-or-one. So for each User there may be a Customer or a Cook record, or both if the User fulfils both roles.

Diagram made here: https://app.quickdatabasediagrams.com/#/schema/t_JngpUcm0-w_dbnW3FzsA [Disclaimer: I work for QuickDatabaseDiagrams]

Answer 2

You can create one more column in user table "userType"[buyer/seller/both].This will help to know current user is acting as a buyer , seller or both. This will help to model system flow as well .