by
Brian Haveri aka bwh2 |
2 October 2006
We will handle adding technology classifications just as
we did the categories. A variety of web design
technologies exist: (X)HTML, PHP, Flash, Photoshop, etc.
Because we want our database to capture what technology
was used for each work, we will create a technologies
table: tbl_tech. Like previous tables, tbl_tech will
have an ID column, tech_id, that is the primary key and
is also set to auto increment. It will only hold
information about technologies.
/***** CREATE tbl_tech *****/
CREATE TABLE `tbl_tech` (
`tech_id` INT NOT NULL AUTO_INCREMENT,
`tech_name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`tech_id`)
)
TYPE = myisam;
/***** End CREATE *****/
/***** INSERT sample data into tbl_tech *****/
INSERT INTO `tbl_tech` (
`tech_id`,
`tech_name`
)
VALUES (
'',
'(X)HTML'
);
INSERT INTO `tbl_tech` (
`tech_id`,
`tech_name`
)
VALUES (
'',
'PHP'
);
INSERT INTO `tbl_tech` (
`tech_id`,
`tech_name`
)
VALUES (
'',
'Flash'
);
INSERT INTO `tbl_tech` (
`tech_id`,
`tech_name`
)
VALUES (
'',
'Illustrator'
);
INSERT INTO `tbl_tech` (
`tech_id`,
`tech_name`
)
VALUES (
'',
'XML'
);
INSERT INTO `tbl_tech` (
`tech_id`,
`tech_name`
)
VALUES (
'',
'MySQL'
);
INSERT INTO `tbl_tech` (
`tech_id`,
`tech_name`
)
VALUES (
'',
'CSS'
);
/***** End INSERT *****/
While it looks like we are inserting blank values for
the tech_id column, we are actually just allowing the
SQL database to generate the tech_id using the auto
increment feature.
Like
tbl_works_categories, we will create tbl_works_tech
which will link tbl_works to tbl_tech. Both work_id and
tech_id are foreign keys within tbl_works_tech.
/***** CREATE tbl_works_tech *****/
CREATE TABLE `tbl_works_tech` (
`work_id` INT NOT NULL,
`tech_id` INT NOT NULL
)
TYPE = myisam;
/***** End CREATE *****/
/***** INSERT sample data into
tbl_works_categories *****/
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'1',
'3'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'1',
'2'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'2',
'3'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'2',
'5'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'3',
'4'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'4',
'1'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'4',
'2'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'4',
'6'
);
INSERT INTO `tbl_works_tech` (
`work_id`,
`tech_id`
)
VALUES (
'4',
'7'
);
/***** End INSERT *****/
To access what technology was used for a
particular project we would structure a query as
follows:
/*
Gets the technology names used with work_id #1
Should return 2 rows with tech_name = 'Flash'
and 'PHP'
*/
SELECT c.tech_name
FROM tbl_works a,
tbl_works_tech b,
tbl_tech c
WHERE a.work_id = 1
AND a.work_id = b.work_id
AND b.tech_id = c.tech_id
/*
Gets the technology names used for works in the
logos category
Should return 1 row with tech_name =
'Illustrator'
*/
SELECT c.tech_name
FROM tbl_works a,
tbl_works_tech b,
tbl_tech c,
tbl_works_categories d,
tbl_categories e
WHERE a.work_id = b.work_id
AND b.tech_id = c.tech_id
AND a.work_id = d.work_id
AND d.category_id = e.category_id
AND e.category_name = 'logos'
Now is a good time to take a deep breath
and reread that join query. If needed, practice writing
join queries on your own. Start by joining just two
tables, then move on to more tables. Joining tables is
an absolute necessity when using a relational database
design. Onwards to the
next page!
|
Relational Database Design - Page 6
