How to design database tables for a Factory class?

Question

How can I design database table structure for a class that varies in its core parameters, when it is created by a factory method?

I have a Factory like this:

class ProductFactory
{
 public function giveMeProduct()
 {
 switch ($this->productLine) {
 case "A": return new AProduct();
 case "B": return new BProduct();
 case "C": return new CProduct();
 case "D": return new DProduct();
 case "E": return new EProduct();
 case "F": return new FOptions();
 default: return new Product();
 }
 }
}
$product = (new ProductFactory("A"))->giveMeProduct();

Each product has different parameters. There are some shared, but not many.

So for example once created, each product contains the following variable options:

______stages___pipes___holder___orientation (flags)
| A | x | | | |
| B | | x | x | x |
| C | | | | x |
| D | | x | x | |
| E | | x | | |
 etc

So I can either design several tables, one for each product, but I think doing so will be quite wasteful in resources (extra tables, files, connecting tables) for just a few parameters in a small little product options class. Or I can cobble all product options into a single table, which will be sparse, like one above. It will be wasteful but in other ways (table space), although seeing that I don't have many options it may be okay.

In a word, I have 6 classes, each with different options, most of which are not shared among classes.

How do I design a table for this class to persistently store and recover data? What patterns can I use?

• Sejanus: Do you expect product parameters to change in the future? Can product acquire new parameters, or lose some of the current ones? Do you expect new products in the future, with yet another slightly different parameter sets?
• Dennis: yes products can acquire new parameters, and might lose current ones, but at a slower pace. Yes there will be new products in the future and those may have their own a new set of parameters as well.

Answer 1

I would solve this with three tables: products, features, and product_features.

Products
id name description 
1223 A abcdefg
1224 B hijklmnop
1225 C qrstuvwxyz
features
id name description 
20 stages such-and-such
21 pipes 
22 holder
23 orientation
product_features
PRIMARY KEY(product_id,feature_id)
CONSTRAINT FK_product_id FOREIGN KEY (product_id) REFERENCES products(id)
CONSTRAINT FK_feature_id FOREIGN KEY (feature_id) REFERENCES features(id)
product_id feature_id
1223 20
1224 21 
1224 22 
1224 23 

• Robert Harvey: This looks like an EAV design. Are you sure that's the right choice?
• Mike: @RobertHarvey I am not sure it is the best for his application; that is up to the OP. This is where my mind went to hold a sparse matrix in a database in a way that is normalized and extensible over time. I am not sure it is strictly EAV since there is no "value" being stored, just a boolean of feature exists or not. Holding a value particular to different products would cause a change to this scheme.
• Dennis: in my application there will be a value as well. Most often 0/1 or in some cases 1, 2, 3, and in other cases other values as well
• Mike: You should add this bit about values to the original question since many people will not read this comment. Gimme a little while and I'll revise my answer for this more complicated scheme.