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. Next: Tangled Relationships >>
More PHP Articles
More By Harish Kamath, (c) Melonfire
|
