What I want to do is record information on computers I build.For example I would like to have the customers details, Computer specs, benchmark results.I have been playing already and have decided I NEED HELP. :'(
so far I have come up with the following.(please don't be shy to criticise)
Database
DROP TABLE IF EXISTS `benchmarks`;
CREATE TABLE IF NOT EXISTS `benchmarks` (
`BenchmarkID` int(10) NOT NULL AUTO_INCREMENT,
`UserBenchAudioTime` float DEFAULT NULL,
`UserBenchAudioSpeed` float DEFAULT NULL,
`UserBenchAudioScore` float DEFAULT NULL,
`UserBenchVideoTime` float DEFAULT NULL,
`UserBenchVideoSpeed` float DEFAULT NULL,
`UserBenchVideoScore` float DEFAULT NULL,
PRIMARY KEY (`BenchmarkID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `computer`;
CREATE TABLE `computer` (
`ComputerID` int(10) NOT NULL AUTO_INCREMENT,
`CustomerID` int(10) NOT NULL,
`CPUID` int(10) NOT NULL,
`MotherboardID` int(10) NOT NULL,
`GraphicsID` int(10) NOT NULL,
`BenchmarkID` int(10) NOT NULL,
`DateBuilt` date DEFAULT NULL,
`ModelNumber` varchar(15) DEFAULT NULL,
`OS` varchar(10) DEFAULT NULL,
`MemorySpeed` int(10) DEFAULT NULL,
`MemoryAmount` int(10) DEFAULT NULL,
`HardDriveType` varchar(10) DEFAULT NULL,
`HardDriveSize` varchar(10) DEFAULT NULL,
PRIMARY KEY (`ComputerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `cpu`;
CREATE TABLE `cpu` (
`CPUID` int(10) NOT NULL AUTO_INCREMENT,
`NumOfCores` int(5) DEFAULT NULL,
`sSpec` varchar(6) DEFAULT NULL,
`CPUSpeed` varchar(50) DEFAULT NULL,
`BusSpeed` int(5) DEFAULT NULL,
`Technology` int(5) DEFAULT NULL,
`L2CacheSize` int(10) DEFAULT NULL,
`L3CacheSize` int(10) DEFAULT NULL,
PRIMARY KEY (`CPUID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `customer`;
CREATE TABLE IF NOT EXISTS `customer` (
`CustomerID` int(10) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(15) DEFAULT NULL,
`LastName` varchar(15) DEFAULT NULL,
`Address` varchar(50) DEFAULT NULL,
`Phone` varchar(12) DEFAULT NULL,
PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `graphics`;
CREATE TABLE `graphics` (
`GraphicsID` int(10) NOT NULL AUTO_INCREMENT,
`Oem` varchar(15) DEFAULT NULL,
`Manufacturer` varchar(25) DEFAULT NULL,
`Model` varchar(25) DEFAULT NULL,
`Gpu` varchar(15) DEFAULT NULL,
`MemoryType` varchar(15) DEFAULT NULL,
`MemorySize` int(5) DEFAULT NULL,
`GpuClock` int(5) DEFAULT NULL,
`MemClock` int(5) DEFAULT NULL,
`BusWidth` int(5) DEFAULT NULL,
`CrossfireSupport` varchar(5) DEFAULT NULL,
`SliSupport` varchar(5) DEFAULT NULL,
PRIMARY KEY (`GraphicsID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `motherboard`;
CREATE TABLE IF NOT EXISTS `motherboard` (
`MotherboardID` int(10) NOT NULL AUTO_INCREMENT,
`Manufacturer` varchar(25) DEFAULT NULL,
`Model` varchar(25) DEFAULT NULL,
`Chipset` varchar(50) DEFAULT NULL,
`PCIExpress16` int(2) DEFAULT NULL,
`PCIExpress8` int(2) DEFAULT NULL,
`PCIExpress4` int(2) DEFAULT NULL,
`PCIExpress1` int(2) DEFAULT NULL,
`PCI` int(2) DEFAULT NULL,
`SATA` int(2) DEFAULT NULL,
`IEEE1394` int(2) DEFAULT NULL,
`USB` int(2) DEFAULT NULL,
`Audio` varchar(25) DEFAULT NULL,
`FormFactor` varchar(15) DEFAULT NULL,
PRIMARY KEY (`MotherboardID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
I had ideas of separating the information.....although while Im not to bad with php i always get stuck on bringing all the info together.For example i can bring together the customer table and computer table using left join but I'm lost after that.Am I heading in the right direction or should I redesign the database.