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.

  1. admin (no need for registration)
  2. doctor (standard number of doctors, no need for registration)
  3. 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) 
acc_id(primary key) 
2nd table
acc_id (foreign key)
doc_info_id (primary key)
3rd table
acc_id (foreign key)
pat_info_id (primary key)
4th table
acc_id (foreign key)
admin_id (primary key)
  • 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 

Accounts type table
   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

Need Your Help

MtGox API and websocketpp

c++ websocket websocket++

I can't get info from MtGox API via WebSockets C++ library named websocketpp:

How do I Validate the File Type of a File Upload? javascript validation file upload

I am using <input type="file" id="fileUpload" runat="server"> to upload a file in an ASP.NET application. I would like to limit the file type of the upload (example: limit to .xls or .xlsx file