MySQL DB design

[tyson.crouch]

-- Table structure for table `attractions`

CREATE TABLE IF NOT EXISTS `attractions` (
  	`attID` int(11) NOT NULL AUTO_INCREMENT,
  	`attName` varchar(255) NOT NULL,
  	`adultPrice` double(5,2) NOT NULL,
  	`childPrice` double(5,2) NOT NULL,
  	`referenceID` varchar(255) NOT NULL,
  	PRIMARY KEY (`attID`)
) ENGINE=InnoDB;

-- Table structure for table `packages`

CREATE TABLE IF NOT EXISTS `packages` (
  	`pacID` int(11) NOT NULL AUTO_INCREMENT,
  	`packagePrice` double NOT NULL,
  	PRIMARY KEY (`pacID`)
) ENGINE=InnoDB;

-- Table Structure for attraction_packages

CREATE TABLE IF NOT EXISTS `attraction_packages` (
`attID` INT(8) NOT NULL,
  	`pacID` INT(8) NOT NULL,
  	INDEX (attID),
            FOREIGN KEY (`attID`) REFERENCES `attractions` (`attID`),
                ON UPDATE CASCADE ON DELETE CASCADE,
        INDEX (`pacID `),
            FOREIGN KEY (`pacID `) REFERENCES `a_packages` (`pacID `),
                ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

Im confused as to which constraint reference option that i should be using.
I have the above mentioned DB set up at the moment, and have set up my constraints however i'm rethinking my FK constraints. Should i be using the cascade reference option or should i be using restrict ?

[luap599]

It depends on the result you are after. Both have very different results. A good refferance to your question can be found in the book ( SQL for MySql Developers ) Written by Rick F. van der Lans: Page 551

Paul

[drjohn]

I notice you don't have a primary key, which would result in MySQL creating an index on the PK automatically.

It looks like you are building a linking table and that the two fields mentioned are in fact a joint primary key. So if you made them a joint primary key, neither would need the index declaration.

[tyson.crouch]

@drjohn, you would be correct in assuming that i am building a linking table to avoid M:M relationships, and i'm using the two fields (attID and pacID) as joint keys. Is the fact that i do not need to declare them as index's because the primaries as index forms themselves?

@laup599 - Thank you for the book reference, i will dl the book as soon as i can and give it a look. thankyou again

[drjohn]

Yes, declaring a column or pair of columns as the primary key automatically means they will be indexed.

[tyson.crouch]

Yes, declaring a column or pair of columns as the primary key automatically means they will be indexed.

Thanks. I appreciate the help