|
|
CREATE TABLE `itemTypes` (
`itID` int(11) NOT NULL AUTO_INCREMENT,
`itemType` varchar(255) NOT NULL,
PRIMARY KEY (`itID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `itemTypes` (`itID`, `itemType`) VALUES
(1, 'Item 1'),
(2, 'Item 2'),
(3, 'Item 3');
CREATE TABLE `clients` (
`cID` int(11) NOT NULL AUTO_INCREMENT,
`clientName` varchar(255) NOT NULL,
PRIMARY KEY (`cID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `clients` (`cID`, `clientName`) VALUES
(1, 'Client 1');
CREATE TABLE `userGroups` (
`ugID` int(11) NOT NULL AUTO_INCREMENT,
`userGroup` varchar(255) NOT NULL,
PRIMARY KEY (`ugID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `userGroups` (`ugID`, `userGroup`) VALUES
(1, 'Group 1'),
(2, 'Group 2');
CREATE TABLE `users` (
`uID` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(255) NOT NULL,
`cid` int(11) NOT NULL,
`ugID` int(11) NOT NULL,
PRIMARY KEY (`uID`),
KEY `cid` (`cid`),
KEY `ugID` (`ugID`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `clients` (`cID`),
CONSTRAINT `users_ibfk_2` FOREIGN KEY (`ugID`) REFERENCES `userGroups` (`ugID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users` (`uID`, `user`, `cid`, `ugID`) VALUES
(1, 'Fred', 1, 1),
(2, 'Sam', 1, 2);
CREATE TABLE `clientItemTypes` (
`cID` int(11) NOT NULL,
`itID` int(11) NOT NULL,
UNIQUE KEY `cID_itID` (`cID`,`itID`),
KEY `itID` (`itID`),
CONSTRAINT `clientItemTypes_ibfk_1` FOREIGN KEY (`cID`) REFERENCES `clients` (`cID`),
CONSTRAINT `clientItemTypes_ibfk_2` FOREIGN KEY (`itID`) REFERENCES `itemTypes` (`itID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `clientItemTypes` (`cID`, `itID`) VALUES
(1, 1),
(1, 2),
(1, 3);
CREATE TABLE `userGroupItemTypes` (
`ugID` int(11) NOT NULL,
`itID` int(11) NOT NULL,
UNIQUE KEY `ugID_itID` (`ugID`,`itID`),
KEY `itID` (`itID`),
CONSTRAINT `userGroupItemTypes_ibfk_1` FOREIGN KEY (`ugID`) REFERENCES `userGroups` (`ugID`),
CONSTRAINT `userGroupItemTypes_ibfk_2` FOREIGN KEY (`itID`) REFERENCES `itemTypes` (`itID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `userGroupItemTypes` (`ugID`, `itID`) VALUES
(2, 1),
(2, 2);
|
