A good database schema

ok so i have this problem that i need to achieve...Here is my site

What i need to do is for each product sold i need to have 2 other products that are either an upgrade or a downgrade.

so there each product has 3 classes standard, business, and premium and based on what the current product is they either increase or decrease in price..

so basically they are customizing each solution. So what i need is figure out the best way to structure my db for this...here is the query i use to pull what i have now

 $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 c.id = pc.category_id
              join subcategory as sc on p.SubCategoryID = sc.SubCategoryID
              WHERE  c.code      = '{$type}_{$count}_{$class}'
              order by sc.ordering";

I was thinking of in the productinfo table which is currently structured like 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 NU开发者_如何学JAVALL,
  `Status` tinyint(4) NOT NULL,
  `PartName` varchar(255) NOT NULL,
  `skip_step` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=164 DEFAULT CHARSET=latin1;

I was thinking off adding three extra fields

class 
product1
product2

so basically i can use the class to tell what the current product is and then the other two fields product1 and product2 which will have the ProductID of the other two products....This seems like it could work but i feel like theres probably a better solution out there then can work a bit better....any ideas input would be strongly appreciated

I would suggest have one table with the Product information. A second table with the class of product (Standard, Business, Premium) and then a third table where you map your client's purchase to the product information ID and the Product Class ID.

So if you need to add a new class of product in the future (Freeware, Premium Deluxe etc) you just add it to the Product Class table and then you can map this in the mapping table.

So when a client purchases a product you map the Product ID and Product Class ID in the mapping table. If they downgrade, you update the mapping table to now map the Product ID to the downgraded Product Class ID.

A few questions...

1. If the product displayed is standard, does it just show the business and premium products? if the product is already the lowest, does it just show upgrades... and if it is premium, does it just show downgrades? If not, does it show a different category of product in order to always show one upgrade and one downgrade?
2. How is it determined which products are associated as alternatives to other products? Is this something that is determined programmatically, or does someone just manually pick out which products are associated?

If the associated products can be determined through logic, I think it would be worth considering not adding the extra fields to the products table, and instead resolve the associations on demand through the queries. The reasoning being that if some criteria of a product were to change, such as the price... then it might no longer be considered an upgrade or downgrade for a product that currently references it... and then you have a data inconsistency issue.

--EDIT--

Thank you for the answers. So if the products associations are chosen manually, and every product will have 2 and only two associations, then your initial design looks good to me (I would just be sure to add the FKs). However, if you think there's a chance that someday products might have a variable number of associations... like one product only has one alternative, while another could have 4 alternatives, then I might put the associations into a separate table. Something like...

CREATE TABLE `associatedproduct` (
  `ProductID` int(11),
  `AssociatedProductID` int(11)
)

That way it wouldn't be set in stone that a product has to have exactly two associations. You could also add additional columns to this table that could describe the association between the two products... like product 'A' is an upgrade, while product 'B' is a premium upgrade.