Database Design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jjones7
Forum Newbie
Posts: 2
Joined: Wed Jun 23, 2010 12:32 am

Database Design

Post by jjones7 »

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 .
User avatar
Sofw_Arch_Dev
Forum Commoner
Posts: 60
Joined: Tue Mar 16, 2010 4:06 pm
Location: San Francisco, California, US

Re: Database Design

Post by Sofw_Arch_Dev »

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;
jjones7
Forum Newbie
Posts: 2
Joined: Wed Jun 23, 2010 12:32 am

Re: Database Design

Post by jjones7 »

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
Post Reply