+3

which database design is better?

let say I have 3 different user type (eg: user, shop, admin) and all 3 required similar information. option 1 : make 3 different table for each user type or option 2 : use 1 table but add another column to differentiate each user type which one is better or both is okay depending the situation and if so under which circumstances should I use each option? any other suggestion is much appreciated?

1/30/2020 5:33:22 PM

Adam

4 Answers

New Answer

+3

If the columns for all 3 are completely different then I would suggest you to go for making 3 separate tables. But if they all have only a single column that is different then probably you should go for a single table. This would help you see the output clearly and all at one place by using simple queries. Whereas for different tables you must use joins to combine the results of the table.

+2

1) It will be useful for you to read this 👇 https://en.m.wikipedia.org/wiki/Database_normalization Maybe: Table 1: Users id | name | typeId --------------------------------- 1 | Tom | 1 --------------------------------- 2 | Jerry | 2 --------------------------------- 3 | Bob | 3 --------------------------------- 4 | Rob | 2 --------------------------------- Table 2: Types id | name --------------------------------- 1 | admin --------------------------------- 2 | customer --------------------------------- 3 | shop assistant ---------------------------------

+2

2) And if you need to get information about all the customers, then just do it: SELECT * FROM Users WHERE typeId = 2; or SELECT * FROM Users WHERE typeId IN ( SELECT id FROM Types WHERE name = "customer" ); ☝🔥In general, the criterion for the quality construction of the database is how easily you can get the necessary information from it. 😉

0