[SOLVED] computer specs + benchmark results design

[baxr6]

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.

[bundyxc]

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.

[l0ve2hat3]

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

 

[baxr6]

argh ok...thank you very much.