by
Brian Haveri aka bwh2 |
2 October 2006
At this point, you might have an idea what's coming
next: tbl_clients. Appropriately, tbl_clients will have
client_id as the primary key set to auto increment.
/***** CREATE tbl_clients *****/
CREATE TABLE `tbl_clients` (
`client_id` INT NOT NULL AUTO_INCREMENT,
`client_name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`client_id`)
)
TYPE = myisam;
/***** End CREATE *****/
/***** INSERT sample data into tbl_clients
*****/
INSERT INTO `tbl_clients` (
`client_id`, `client_name`
)
VALUES (
'',
'Johnson Photography'
);
INSERT INTO `tbl_clients` (
`client_id`, `client_name`
)
VALUES (
'',
'Super Duper Company'
);
INSERT INTO `tbl_clients` (
`client_id`, `client_name`
)
VALUES (
'',
'Smith News Service'
);
/***** End INSERT *****/
As with categories and technologies, we
will use a mapping table: tbl_works_clients to map which
works were for which clients. Even though one work may
only belong to one client, a good relational design will
still show the relationship in a separate table instead
of placing it in tbl_works.
/***** CREATE tbl_works_clients *****/
CREATE TABLE `tbl_works_clients` (
`work_id` INT NOT NULL,
`client_id` INT NOT NULL
)
TYPE = myisam;
/***** End CREATE *****/
/***** INSERT sample data into tbl_works_clients
*****/
INSERT INTO `tbl_works_clients` (
`work_id`,
`client_id`
)
VALUES (
'1',
'1'
);
INSERT INTO `tbl_works_clients` (
`work_id`,
`client_id`
)
VALUES (
'2',
'3'
);
INSERT INTO `tbl_works_clients` (
`work_id`,
`client_id`
)
VALUES (
'3',
'2'
);
INSERT INTO `tbl_works_clients` (
`work_id`,
`client_id`
)
VALUES (
'4',
'2'
);
/***** End INSERT *****/
Then we can query across our client
tables just as we did with categories and technologies.
For instance:
/*
Gets the client names associated with work_id #1
Should return 1 row with the client_name =
'Johnson Photography'
*/
SELECT c.client_name
FROM tbl_works a,
tbl_works_clients b,
tbl_clients c
WHERE a.work_id = 1
AND a.work_id = b.work_id
AND b.client_id = c.client_id
Onwards to the
next page!
|
Relational Database Design - Page 7
