User table with multiple groups with various unique fields in each

1

So, I have user[id, email, password, group_id], the default group_id is 0, normal user;

This user can be either an admin, normal user, or member.

normal[id, user_id, firstname, lastname]

admin[id, user_id, handle]

member[id, user_id, contact number, is_admin].

is this the best approach, or is there a better way.

2

Well, what the “best” approach is always debatable, but I have used similar constructs before. The extra tables are linked as belongsTo/hasOne to the user table. It’s certainly more flexible and elegant than stashing everything into the user table.

3

thanks Bankai, perhaps I should have said better approach. but it looks like im on the right track. and with cake bake it do that relationship belongsTo/hasOne automatically.

4

I would argue that the best approch would be to create 4 tables.
Users [id,email,password]
Groups[id,name,role_id]
UserGroups[user_id,group_id]
Roles [id,name]

Doing it this way will allow you to do the following.

Creating 2 roles, Admin and User.

Creating multiple groups and assigning them to either Admin or User.
For example, you could create a Merchant group and a Normal group which both have the role User.

This is usefull if you only want Admin roles to access your backend, Normal group only to access the frontend, and Merchants would have their prices shown without VAT.