so i've this issue that i have to achieve...Here's my site

What i have to do is perfect for each product offered i have to have 2 other items which are either upgrading or perhaps a downgrade.

so there each product has 3 classes standard, business, and premium and according to exactly what the current method is they either increase or reduction in cost..

so essentially they're designing each solution. What exactly i want is determine the easiest method to structure my db with this...this is actually the query i personally use to drag things i have finally

 $query = "SELECT p.ProductName, p.price, pc.quantity, p.ProductImage, p.Features
            FROM productinfo as p
              join preconfig_categories as pc on p.ProductID = pc.product_id
              join preconfig as c on = pc.category_id
              join subcategory as sc on p.SubCategoryID = sc.SubCategoryID
              WHERE  c.code      = '{$type}_{$count}_{$class}'
              order by sc.ordering";

I believed of within the productinfo table that is presently structured such as this

CREATE TABLE `productinfo` (
  `ProductID` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(255) NOT NULL,
  `ProductImage` varchar(255) NOT NULL,
  `CategoryID` int(11) NOT NULL,
  `SubCategoryID` int(11) NOT NULL,
  `ProductBrief` varchar(255) NOT NULL,
  `Features` text NOT NULL,
  `Specifications` text NOT NULL,
  `Reviews` text NOT NULL,
  `Price` varchar(255) NOT NULL,
  `Status` tinyint(4) NOT NULL,
  `PartName` varchar(255) NOT NULL,
  `skip_step` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`ProductID`)

I believed off adding three extra fields


so essentially i'm able to make use of the class to inform exactly what the current method is and so the other two fields product1 and product2 that will possess the ProductID from the other two items....This appears enjoy it perform however i seem like theres most likely a much better solution available then can function a little better....any ideas input could be strongly appreciated

I recommend have one table using the Product information. Another table using the class of product (Standard, Business, Premium) after which another table in which you map your client's purchase towards the product information ID and also the Product Class ID.

So if you want to give a new class of product later on (Free software, Premium Luxurious etc) you simply combine it with the merchandise Class table and you can map this within the mapping table.

Then when a customer purchases an item you map the merchandise ID and Product Class ID within the mapping table. When they downgrade, you update the mapping table to now map the merchandise ID towards the downgraded Product Class ID.