Help With Database Design

[Andy-H]

Hi, I have attached a partial of my database design schema, I can't think of a way to re-design it flexibly without using EAV, any suggestions?

[Christian F.]

First suggestion would be to actually attach the design.

[Andy-H]

http://i45.tinypic.com/30mq1lh.png

 

Sorry, it's this new forum software, I did actually attach it :/

[ignace]

What are the relations between both tables? Like does a product have multiple reference_number's? Or just one? What is meant with capacity? .. Please provide all information.

 

IMO EAV is not the answer here. When I look at the (partial) design I feel that not all tables/relations have yet been identified and thus causes confusion.

 

Identify all tables and their relations to make the model speak out and clear up the confusion.

 

EDIT:

Maybe some of these options solve your problem (except EAV, check comments below):

http://stackoverflow.com/a/695860

Edited September 26, 2012 by ignace

[Andy-H]

Products are plastic containers, capacity refers to the capacity of the container, i.e. 1000ml, I am only focusing on the product-product variant schema as I am happy with the rest of the database design.

 

So we have a main table, holding product-specific data, variants are the same product, i.e. same shape bottle, make/manufacturer etc. but can vary in capacity etc. each variant must have a reference number, but the rest of the data can be NULL.

 

I also need the ability to search products by variant values, i.e. where capacity = 1000 (ml) and would prefer if I could retrieve the filter options dynamically, hence my leaning towards EAV.

 

I read the stack overflow post but none of the options screamed eureka at me.

Edited September 26, 2012 by Andy-H

[ignace]

Since you mention that capacity is dependent upon refno. it's clear that you need a product_refno table:

 

product_refno (refno_id, prod_id, capacity, ..)

 

would prefer if I could retrieve the filter options dynamically, hence my leaning towards EAV.

 

This will not work as you might expect for the same reason: SHOW COLUMNS FROM TABLE `product_refno` would not work either. How will you for example make sure you only return the fields that you want to allow to filter upon? At some point you will still need to add in custom coding to hide certain columns.

 

Which defeats your only argument for EAV. Don't be lazy, properly normalize your DB.

[Andy-H]

Reference no. is nothing special, just another variant field, the only difference is that it's required, how about this:

 

products - product_variants - variant_attributes - attributes

id - id - variant_id - id

... - product_id - attribute_id - attribute

- value

Then I only need to add the required fields, eliminating the need for null fields, and I could run a

 

#get filters
SELECT id, attribute FROM attributes
#get filter options
SELECT DISTINCT value FROM variant_attributes WHERE attribute_id = $id
#get filtered products
SELECT p.* FROM products p
INNER JOIN product_variants pv ON ( p.id = pv.product_id )
INNER JOIN variant_attributes va ON ( pv.id = va.variant_id )
WHERE va.attribute_id = $filter_key AND va.value = $filter_value

Edited September 26, 2012 by Andy-H