Table Structure

From: john <nospam_at_nospam.com>
Date: Tue, 25 Mar 2003 15:14:17 -0000
Message-ID: <%l_fa.1907$yF3.189601@stones.force9.net>

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.

Any thoughts comments on this would be greatly appreciated.

Thanks for your time. Received on Tue Mar 25 2003 - 09:14:17 CST