Database Design model a Customer, Cook, User relationship
database, database-design, mysql, postgresql
Solution
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]
Problem
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: - Make 3 tables. User, Customer, Cook. - Make 2 tables. User, Customer. - 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: