Re: Help with another query!
From: Heinz Huber <XhhuberX_at_no-racon-linz.at-no>
Date: Thu, 27 Mar 2003 07:54:39 +0100
Message-ID: <3e82a030$0$21592$91cee783_at_newsreader01.highway.telekom.at>
>>john wrote:
>>
>>>Hi,
>>>
>>>I have the following table structure, as my installation of MySQL doesnt
>>>support Foreign keys i have to maintain the referential integrity
>>
>>>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
>>
>>>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.
>>
>>hth,
>>Heinz
>>
>>
>>
>>>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'
>>>#
>>>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)
>>>);
Received on Thu Mar 27 2003 - 07:54:39 CET
Date: Thu, 27 Mar 2003 07:54:39 +0100
Message-ID: <3e82a030$0$21592$91cee783_at_newsreader01.highway.telekom.at>
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.
Regards,
Heinz
> "Heinz Huber" <XhhuberX_at_no-racon-linz.at-no> wrote in message > news:3e800c16$0$21198$91cee783_at_newsreader01.highway.telekom.at... >
>>john wrote:
>>
>>>Hi,
>>>
>>>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.
>>
>>hth,
>>Heinz
>>
>>
>>
>>>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'
>>>#
>>>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)
>>>);
Received on Thu Mar 27 2003 - 07:54:39 CET