Shaun, I took a quick look at your database layout and noticed that Practice_ID and Clinical_Trial_ID are repeated in the Booking table. This isn't necessary because the Booking table links to the Project table which contains this information. In the Project table, Practice_ID and Clinical_Trial_ID should probably just be foreign keys and not primary keys to that table. Same thing for User_ID and Project_ID in the Booking table. To keep track of the data you will need to create a new table which is linked to the Clinical_Trial table. For instance you could create a table called Clinical_Data which has the following fields: * Clinical_Data_ID (PK) * Clinical_Trial_ID (FK) * Data_Description - field that describes a piece of collected data (e.g. blood pressure before hypnosis) Is the User table where the client information goes? You also need to create a joining table between the User (or whatever table contains the client info.) table and the Clinical_Data table. ---------------------- Beverly Steiner steiner277@charter.net -----Original Message----- From: shaun [mailto:shaun@mania.plus.com] Sent: Monday, March 10, 2003 8:53 AM To: php-db@lists.php.net Subject: Database design Hi, I am creating a database for a web application. The idea for the application is for a company administrator to be able to log into the site and allocate staff to a project which will be a clinical trial at a particular practice. Staff will be able to log in and update the status of the project they are working on and clients will be able to log in and book a member of staff. Administrators will be able to add / edit / delete - staff / clients / clinical trials. Here is my database diagram: http://www.mania.plus.com My problem is each different clinical trail will need to have different data collected for it. How could I handle this in the database model? The administrator would like to be able to edit the data collected for each trial via the site... Any other comments on my database model would be appreciated as this is my weakest area of web development by far! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
