Hello,
Im new to MySQL and PHP and need to build a database which stores many many different types of interests which a customer may have. Whats more, the interests may be subcategorised. For example....
As a customer I may be interested in Construction. Within construction, I may be particularly interested in bricks, cement, mortar, walls and lighting.
I may also be interested in Cars. Within cars I may have aparticluar interest for Ferraris, Vipers, Nissan Sunnys and plastic pigs.
I was hoping to get suggestions over how to structure a database to a) capture the fact that a uer may have many interests ..... and b) that these interests can be sub categorised into other interests.
I thought about having 2 tables.. One with the users general information in.. and another with the list of interests in.
eg:
Table 1
Name: Address: Postcode:
Jon 10, nowhere lane WN76 8LF
Table 2
Interests
I have no idea how to structure the second table
Can anyone help me with a schema which could capture this data ?
Thanks for your help .
Database Design
Moderator: General Moderators
- Sofw_Arch_Dev
- Forum Commoner
- Posts: 60
- Joined: Tue Mar 16, 2010 4:06 pm
- Location: San Francisco, California, US
Re: Database Design
Shooting from the hip I'm envisioning three tables. I don't know the complete set of requirements and expectations, so there may be some tweaks required, but here's what I'm thinking (using MySQL). This will accommodate customers and hierarchical interests.
Code: Select all
DROP TABLE IF EXISTS `customer_interests`;
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`customer_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(32) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `interests`;
CREATE TABLE `interests` (
`interest_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`parent_interest_id` BIGINT(20) UNSIGNED NULL,
`title` VARCHAR(128) NOT NULL,
PRIMARY KEY (`interest_id`),
INDEX `ix_interests_parent_interest_id` (`parent_interest_id` ASC),
UNIQUE( `interest_id`,`parent_interest_id` )
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `customer_interests` (
`customer_id` BIGINT(20) UNSIGNED NOT NULL,
`interest_id` BIGINT(20) UNSIGNED NOT NULL,
UNIQUE( `customer_id`,`interest_id` ),
CONSTRAINT `fk_customer_interests_customer_id`
FOREIGN KEY (`customer_id` )
REFERENCES `customer` (`customer_id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_customer_interests_interest_id`
FOREIGN KEY (`interest_id` )
REFERENCES `interests` (`interest_id` )
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Re: Database Design
Hey thanks very much
All I have to do now is work out to drop multi select box entries from a form into such a table..
ie.
Any ideas as to a decent insert query?
CHeers
All I have to do now is work out to drop multi select box entries from a form into such a table..
ie.
Any ideas as to a decent insert query?
CHeers