Jump to content

Help With Database Design


Recommended Posts

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.



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


Edited by ignace
Link to comment
Share on other sites

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 by Andy-H
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Andy-H
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.