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) 
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.

Answers


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


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?

asp.net 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

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.