Re: Help with another query!

From: Heinz Huber <>
Date: Thu, 27 Mar 2003 07:54:39 +0100
Message-ID: <3e82a030$0$21592$>

john wrote:

> thanks for your reply,
> it is ok to change the table structure, and i would rather get it right now
> rather than later!
> So if i implement you suggestion, the primary key for the Bookings table
> would be User_ID, Project_ID and Booking_ID or would it be just Booking_ID.
> As there will only be one booking per day i could use Booking_Date also...

If you're absolutely sure that there is and ever will be only one booking per allocation and day, you could use User_ID, Project_ID and Booking_Date as a primary key.

On the other hand, this table is not a simple link table and I've made the experience that the uniqueness tends to disappear about one month after you go into producation ;-)

Therefore, I'd keep the Booking_ID and use it as a primary key. In addition to that you can create a unique index on the alternative key of User_ID, Project_ID, Booking_Date. This way, you don't have to change the keys when the business rules change. You can simply drop the secondary index.


> "Heinz Huber" <> wrote in message
> news:3e800c16$0$21198$

>>john wrote:
>>>I have the following table structure, as my installation of MySQL doesnt
>>>support Foreign keys i have to maintain the referential integrity
> myself. So

>>>if i delete a user, how can i make sure that the allocations from the
>>>allocations table where the user_id is the same as the one being deleted
> and

>>>that bookings are deleted where the allocations_id is the same as the
>>>allocations deleted for that user_id?
>>Two possibilities:
>>1. Change the table structure and don't generate a surrogate key for
>>every table. Then the primary key for Allocations would be Project_ID,
>>User_ID. As soon as this is your primary key, you also have these two
>>columns in the table WMS_Bookings and the problem of knowing which user
>>a booking belongs to is trivial.
>>2. If you can't change the table structure any more, use the following
>>SQL (attention MySQL not standard!!):
>>DELETE FROM WMS_Users, WMS_Allocations, WMS_Booking
>> WHERE WMS_Users.User_ID = _at_user_id AND
>> WMS_Users.User_ID = WMS_Allocations.User_ID AND
>> WMS_Allocations.Allocation_ID = WMS_Booking.Allocations_ID;
>>This should delete the respective rows from all three tables.
>>By the way, I wouldn't use cascading referential identity without
>>thorough performance tests. AFAIK, most of the cascading implementations
>>are performance killers since they cascade row by row.
>>>Thanks for your help
>>># ---------- 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'
>>> 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)
Received on Thu Mar 27 2003 - 07:54:39 CET

Original text of this message