Jump to content

[SOLVED] computer specs + benchmark results design


Recommended Posts

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.

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

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.