I'm new for the MySQL and Php.I have to complete project for my class.Please help me to create table structure for doctors e channeling system. Actually I don't have and idea from where i have start this project.I thought first i have to design table structure for the project.I need to create front end and back end.

I have created some tables structure and I need to know how create relationship between tables. I have a following problems.

01)Need create relationship between users and doctors.Users can select one or many doctors from list box and when he login to website need show only selected doctors details(name.specializations,Hospital)

02)Doctors can be specialist for one or many specialization.

03)Search option should be by doctors by specializations by day(Monday to Sunday)If Monday select need show whole Mondays in month and availability.

I think i have to create many more tables for relationship.But i don't know how to create table structure for this.Please help me for create table structure.

Thanks & Regards,

Chaminda7245

This post has been edited by chaminda7245: 9 Oct, 2008 - 06:45 PM

You did create the needed relationships, the two tables in the lower left where you link doctor_to_users and spec_to_doc. Those are known as conjunction tables and are used to link the id of a user to the id of a doctor. This table can have none, one or many entries showing the user has no doctors, one doctor, or many doctors.

Same with the specializations. You have a doctor id and a specialization id and link the doctors to specialties. A doctor can have no specialty, one specialty or potentially more than one specialty.

The third part there is going to be setup using a query where you link the doctors to their specialties (using the conjunction table spec_to_doc to link doctors to their specialties) and then link to the day of the week the doctor is scheduled for. This will involve another conjunction table.

The only thing I have to immediately say about your tables are the fields doc_to_usr_id in the users table and spec_to_doc_id in the doctors table should not be there. They are not needed. The primary key of each table is all you need and those are the keys that will be found in the conjunction tables. The user and doctors tables are unaware of the conjunction tables. They just represent a user or a doctor's fields.

Hope that helps you out.

Thanks for the reply.How can I insert many doctors from list box to the doctor_to_users conjunction table.

ah, that is where it becomes application specific.
In a listbox it is possible to make them 'multi-select', so that you can select more then one element.
This has its disadvantages.
If the list is large, and you have preselected the existing entries, say dermatology and uhmmm zooology (for the examples sake), a use might not see the second selected item and forget to use shift when doing a selection. This can lead to specializations being inadvertedly disabled.

A more intuitive approach would be to have TWO listboxes.
One contains all specializations that the doctor does not have. You can select one and press an add button. This will add the specialization+doctor combination to the conjunction table.
The second contains all specializations that the doctor does have. You can select one and press a delete button. This will remove the specialization+doctor combination from the conjunction table.