3种不同用户类型的数据库设计指南

Question

I am creating my first web application with mySQL & PHP and just looking for some guidance to ensure I'm on the right lines... or not.

It will have 3 types of user; members, clubs and admins.

I currently have them all logging in through the same log in page and once logged in they go to the same 'dashboard' but the content of the dashboard is changed via conditional statements against the users user type. Is this a sensible idea in terms of security etc?

My current simplified table structure is:

users   {id, username, hashed_password}
members {id, user_id, first_name, last_name, email, address, tel, cleared_activities}
clubs   {id, user_id, club_name, address, tel, available_activities}
admins  {id, user_id, first_name, last_name, email, address, tel}

Could this table design be improved upon? I'm conscious of the similar fields, especially amongst members and admins and this makes me think there is likely a better way.

Many Thanks

Answer 1

Since all members and admins are users, I would use one table for user, members and admins adding one extra field "usertype".

Then I would add a table only for members, that would hold all extra fields that are member specific.

users       {id, username, hashed_password, first_name, last_name, email, address, tel, user_type}
member_data {user_id, cleared_activities}
clubs       {id, user_id, club_name, address, tel, available_activities}

If the extra member fields are just the one you mention, you could also keep it in the users table. For the users that are not of type member, this field will have NULL values.

users       {id, username, hashed_password, first_name, last_name, email, address, tel, user_type, cleared_activities}
clubs       {id, user_id, club_name, address, tel, available_activities}