Merge two results in a MySQL query if the records are related by a field value

Question

We have a products table. Users can create new products as copies of existing products.

Instead of simply duplicating this data, we're thinking in order to minimize database size, we would store only the differences from the "parent" product. (were talking thousands of products)

My thinking is that, for each new "child" product, we create a new record in that same table which has a "parent" field which has the ID of the parent product.

So, when querying for the "child" product, is there a way to merge the results so that any empty fields in the child record will be taken from the parent?

(I hope this makes sense)

Answer 1

Yes, you can do this.

Say for example Your table name is Product and you want to retrieve name of child product, Then you can query as,

select IF(c.productName = '',p.productName,c.productName) as childProductName
from Products p,Products c
where c.ID = p.ParentID 

Similarly you can do this for other fields.

Answer 2

I would anticipate that you'd want to have child products of child products (e.g. product C is based on product B, which is in turn based on product A.) And there would be children of those and so on (especially with user generated content.) This could get out of hand very quickly and require you to make either long cumbersome queries or collect the data with code rather than SQL queries.

I'm just offering this as a consideration because the saving is size often yield a cost of processing time. Just be sure you consider this before you jump into something that can't easily be undone.