Unify specific tables in one product table

If this were my design decision, I would probably go with more of an 'Option C' (modified option a).

First, why not 'Option B':

For one thing, I like the clarity that each product has it's own table affords. If it's all one big table with a field to determine the type, the relation isn't as clear.

For another, the indexing strategy would always require that type field to be listed. Since it's only 4 types, the index cardinality is extremely low (SELECT * FROM product_table WHERE type='X' is basically doing a full table scan anyway)

Option C

• Create a parent table that holds only the columns that all types share
• Create each product type as it's own table with their individual columns, with one extra: A link to the parent table
• Create each 'link' table: Product_Option, Model_option, etc with links to the respective keys.
• For those with reciprocal links (MODEL_OPTION, OPTION_MODEL) go ahead and create those tables as well. This will add clarity in your joins for anyone looking at it.

The downside is the complexity of making sure to avoid orphans when things are updated/deleted, and initially designing the queries that use these tables.

As a horse_with_no_name suggested, I would also go with hstore if you are using PostgreSQL becuase I think its the easiest to morph back to regular table structure if you decide you need that.

There are a couple of features that make hstore pretty nice for your problem:

1) Indexing is pretty fast

2) You can query hstore column with something like

SELECT p.product_name, properties->'color' As color, properties->'size'::numeric As size
     FROM products;

So makes it good for reporting simply by wrapping your different product lines in a view

3) The main downside with hstore is keeping track of properties specific to a particular product type, however you can easily compensate for this with PostgreSQL type system

as demonstrated here: https://stackoverflow.com/questions/16092890/how-can-i-translate-a-postgresql-hstore-column-into-a-row

So you can define say a properties type for shoes called CREATE TYPE shoe_properties(color text, size numeric);

or just use a lookup table of property types and their corresponding data types for editing purposes. Then if you decide later you really want to go with the separate table for properties approach, your job would be simple by just creating a typed table from your type and using popular_record to morph your hstore into a row for insert into table.