baxr6 Posted September 7, 2009 Share Posted September 7, 2009 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. Quote Link to comment Share on other sites More sharing options...
bundyxc Posted September 7, 2009 Share Posted September 7, 2009 If all of the data is related, I don't see why you'd need to use two tables. By the way, this topic should have been posted in the MySQL section. I'm sure a friendly neighborhood mod will move it for you though. Quote Link to comment Share on other sites More sharing options...
l0ve2hat3 Posted September 8, 2009 Share Posted September 8, 2009 You should have 3 tables... 1. computer - merge tables computer, cpu, graphics, motherboard in to this table. obviously you can remove fields like cpu_id and graphics_id. Although if you are dealing with computers with multiple cpu's or graphic cards, then keep those table separate. You can also remove benchmarkID 2. customer - keep the same 3. benchmarks - keep the same but add a computerID field The reason for keeping the tables separate is because what if a customer has multiple computers then you want to be able to use the same customer information and what if you want to run multiple benchmark test, same idea... Once you have the tables setup with the data you want in them you can run queries like: SELECT * FROM `customer` LEFT JOIN `computer` ON `computer`.`customerID`= `customer`.`customerID` LEFT JOIN `benchmarks` ON `benchmarks`.`computerID` = `computer`.`computerID` WHERE `customer`.`customerID`='1' Although your going to have conflicting field names (ie. `computer`.`customerID` and `customer`.`customerID`) so instead of select *, select only the fields that you need Quote Link to comment Share on other sites More sharing options...
baxr6 Posted September 8, 2009 Author Share Posted September 8, 2009 argh ok...thank you very much. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.