Register - Login Database Scheme
Here is what I want to ask:
I want to make a system to register patients so then they will be able to login. I have 3 type of users though.
- admin (no need for registration)
- doctor (standard number of doctors, no need for registration)
- patient (they will be registered)
I want to keep more info for them than just id, username, password, email. I am thinking of having more than 1 tables to do this and link them with primary and foreign keys:
1st table accounts (it will store the login data) Example: acc_id(primary key) acc_password acc_username acc_type
2nd table doctors_extra_info Example: acc_id (foreign key) doc_info_id (primary key) doc_name ... ...
3rd table patients_extra_info Example: acc_id (foreign key) pat_info_id (primary key) pat_name ... ...
4th table admin_info Example: acc_id (foreign key) admin_id (primary key) admin_email
- a. Which is the best way of doing this?
- b. In the part of registration, how to deal with primary and foreign keys? Two insert commands in two different tables? [In order to have the same acc_id in the account table and the extra info table]
- c. At the login part, I need to check the type of user and redirect (header(Location: ..);) to a page? Is this the right way of doing it?
Any suggestions? Thank you.
If you're using PHP then when you insert a record you can instantly retrieve the ID created using mysql_insert_id(). You then use this to create other records as your foreign key.
With regards to redirects, I'd simply get the user type from the database and then check the type of user and redirect to page required.
Generally though the tables you have created do not correlate properly. Remember the defining thing about the people using the system is that they are a person, and shouldn't be deined by their job role. They should have a account_type_id linking to another table. Otherwise you have three tables essentially holding the same information.
For example you should have your tables like this
User table user_id first_name last_name email account_type_id* Accounts type table user_id account_type_id* account_type //e.g. patient, doctor, admin etc
This means now that you can easily extend the database with new tables, user access levels, new columns without having to duplicate the same column across three tables and so on. Try reading up on database normalization. A very good video from youtube is http://www.youtube.com/watch?v=fg7r3DgS3rA