Hi, I would be very grateful if someone could look at my table design for me please. The sytem will be used to allow users to log in to a web site and book staff. Here is my design so far. # ---------- MySQL dump ---------- # # Table structure for table 'WMS_Allocations' # CREATE TABLE WMS_Allocations ( Allocation_ID int(11) DEFAULT '' NOT NULL auto_increment, Project_ID int(11) DEFAULT '0' NOT NULL , User_ID int(11) DEFAULT '0' NOT NULL , PRIMARY KEY (Allocation_ID), KEY Project_ID (Project_ID,User_ID) ); # # Table structure for table 'WMS_Bookings' # CREATE TABLE WMS_Bookings ( Booking_ID int(11) DEFAULT '' NOT NULL auto_increment, Booking_Date date DEFAULT '0000-00-00' NOT NULL , PCT_address varchar(255) , PCT_postcode varchar(255) , PCT_telephone varchar(255) , PCT_manager varchar(255) , PCT_gp varchar(255) , P_address varchar(255) , P_postcode varchar(255) , P_telephone varchar(255) , P_manager varchar(255) , P_gp varchar(255) , Allocation_ID int(11) , PRIMARY KEY (Booking_ID), KEY Allocation_ID (Allocation_ID) ); # # Table structure for table 'WMS_Projects' # CREATE TABLE WMS_Projects ( Project_ID int(11) DEFAULT '' NOT NULL auto_increment, Project_Name varchar(255) , PRIMARY KEY (Project_ID) ); # # Table structure for table 'WMS_User' # CREATE TABLE WMS_User ( User_ID int(11) DEFAULT '' NOT NULL auto_increment, User_Username varchar(100) DEFAULT '' NOT NULL , User_Password varchar(100) DEFAULT '' NOT NULL , User_Name varchar(100) DEFAULT '' NOT NULL , User_Type int(11) DEFAULT '0' NOT NULL , User_Email varchar(100) , PRIMARY KEY (User_ID), UNIQUE User_Username (User_Username) ); A user could be a client, a staff member, or an administrator. Clients can log in and book staff to their own projects. Administrators can log in, allocate staff to any project, add/edit/delete - projects/staff/clients, and book staff on a project they are allocated to. A staff member will be able to log in and complete a booking they have undertaken and add in data needed for the project. At the moment i have a single table to incorporate all three types of user but i think i need to change this as the relationship between a client and a project is different to a staff member/administrator and a project. A client can have many projects but a project will only belong to one client where as a staff member/administrator could be allocated to many projects and project could be allocated to many staff members/administrators. My MySQL installation doesnt support Foreign Keys which makes life a little difficult! Any thoughts comments on this would be greatly appreciated. Thanks for your time. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
