Doing More with phpMyAdmin (Part 2) - Total Recall (
Page 3 of 7 ) Before I begin, a quick
introduction to the tables I'll be using throughout this tutorial seems to be in
order. In order to explain the new features of phpMyAdmin, I'll be using a
fictitious company's accounting database, which consists of the following four
tables:
- The
services table: The fictitious system under discussion
consists of a service company's accounting database. This company offers
customers a number of outsourced services, each of which is associated with a
fee and has a unique service ID. This information is stored in a
services table, which looks like this:
CREATE TABLE `services` (
`sid` tinyint(4) NOT NULL default '0',
`sname` varchar(255) NOT NULL default '',
`sfee` float(6,2) NOT NULL default '0.00',
PRIMARY KEY (`sid`)
) TYPE=MyISAM;
INSERT INTO `services` VALUES
(1, 'Accounting', '1500.00');
INSERT INTO `services` VALUES
(2, 'Recruitment', '500.00');
INSERT INTO `services` VALUES
(3, 'Data Management', '300.00');
INSERT INTO `services` VALUES
(4, 'Administration', '500.00');
INSERT INTO `services` VALUES
(5, 'Customer Support', '2500.00');
INSERT INTO `services` VALUES
(6, 'Security', '600.00');
- The
clients table: The company also has a list of its current
clients stored in a separate clients table. Each client is
identified with a unique customer ID.
CREATE TABLE `clients` (
`cid` tinyint(4) NOT NULL default '0',
`cname` varchar(255) NOT NULL default '',
PRIMARY KEY (`cid`)
) TYPE=MyISAM;
INSERT INTO `clients` VALUES (101, 'JV Real Estate');
INSERT INTO `clients` VALUES (102, 'ABC Talent Agency');
INSERT INTO `clients` VALUES (103, 'DMW Trading');
INSERT INTO `clients` VALUES (104, 'Rabbit Foods Inc');
INSERT INTO `clients` VALUES (110, 'Sharp Eyes Detective Agency');
- The
branches table: Each customer may have one or more branch
offices. The branches table lists the branch offices per customer,
together with each branch's location. Each branch has a description, a unique
branch ID, and a foreign key reference to the customer ID.
CREATE TABLE `branches` (
`bid` int(8) NOT NULL default '0',
`cid` tinyint(4) NOT NULL default '0',
`bdesc` varchar(255) NOT NULL default '',
`bloc` char(3) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO `branches`
VALUES (1011, 101, 'Corporate HQ', 'CA');
INSERT INTO `branches`
VALUES (1012, 101, 'Accounting Department', 'NY');
INSERT INTO `branches`
VALUES (1013, 101, 'Customer Grievances Department', 'KA');
INSERT INTO `branches`
VALUES (1041, 104, 'Branch Office (East)', 'MA');
INSERT INTO `branches`
VALUES (1042, 104, 'Branch Office (West)', 'CA');
INSERT INTO `branches`
VALUES (1101, 110, 'Head Office', 'CA');
INSERT INTO `branches`
VALUES (1031, 103, 'N Region HO', 'ME');
INSERT INTO `branches`
VALUES (1032, 103, 'NE Region HO', 'CT');
INSERT INTO `branches`
VALUES (1033, 103, 'NW Region HO', 'NY');
- The
branches_services table: Services supplied to each branch
office are listed in this table, which contains pairs of branch IDs and service
IDs (foreign keys into the branches and services table
respectively).
CREATE TABLE `branches_services` (
`bid` int(8) NOT NULL default '0',
`sid` tinyint(4) NOT NULL default '0'
) TYPE=MyISAM;
INSERT INTO `branches_services` VALUES (1011, 1);
INSERT INTO `branches_services` VALUES (1011, 2);
INSERT INTO `branches_services` VALUES (1011, 3);
INSERT INTO `branches_services` VALUES (1011, 6);
INSERT INTO `branches_services` VALUES (1012, 1);
INSERT INTO `branches_services` VALUES (1013, 5);
INSERT INTO `branches_services` VALUES (1041, 1);
INSERT INTO `branches_services` VALUES (1041, 4);
INSERT INTO `branches_services` VALUES (1042, 1);
INSERT INTO `branches_services` VALUES (1042, 6);
INSERT INTO `branches_services` VALUES (1101, 1);
INSERT INTO `branches_services` VALUES (1031, 2);
INSERT INTO `branches_services` VALUES (1031, 3);
INSERT INTO `branches_services` VALUES (1031, 4);
INSERT INTO `branches_services` VALUES (1032, 3);
INSERT INTO `branches_services` VALUES (1033, 4);
Now, let's see what we can do with this.
|
