I am designing database for an web application, I have designed the product and specification details database as follows,

create table pr_size
    size_id             int not null auto_increment,
    value               varchar(256) not null default 'No Size Specified',
    is_active           bit(1) default 1,
    primary key (size_id)

create table pr_color
    color_id            int not null auto_increment,
    color_value         varchar(256) not null default 'No color specified',
    is_active           bit(1) default 1,
    primary key(color_id)

create table pr_measurement_unit
    mes_unit_id         int not null auto_increment,
    unit                varchar(10),
    is_active           bit(1) default 1,
    primary key (mes_unit_id)

create table pr_dimension
    dimension_id        int not null auto_increment,
    mes_unit_id         int not null default 0,
    dimension_value     char(10) not null default 'No Value',
    is_active           bit(1) default 1,
    primary key (dimension_id),
    foreign key (mes_unit_id) references pr_measurement_unit (mes_unit_id)

create table pr_product_features
   product_features_id  int not null auto_increment,
   brand_id             int not null default 0,
   color_id             int not null default 0,
   size_id              int not null default 0,
   dimension_id         int not null default 0,
   is_active            bit(1)  default 1,
   primary key (product_features_id),
   foreign key (brand_id)   references pr_brands(brand_id),
   foreign key (color_id)   references pr_color(color_id),
   foreign key (size_id)    references pr_size(size_id),
   foreign key (dimension_id)   references pr_dimension(dimension_id)

create table product
   product_id           int not null auto_increment,
   product_name         varchar(256) not null default 'No product name specified',
   is_active            bit(1) default 1,
   primary key (product_id)

create table pr_product_feature_applicability 
   product_feature_applicability_id int(11) NOT NULL AUTO_INCREMENT,
   product_id int(11) NOT NULL DEFAULT '0',
   product_feature_id int(11) NOT NULL DEFAULT '0',
   dated_thru datetime NOT NULL DEFAULT '9999-12-30 00:00:00',
   is_active bit(1) NOT NULL DEFAULT b'1',
   primary key (product_feature_applicability_id),
   key product_id (product_id),
   key pr_product_feature_idx (product_feature_id),
   constraint pr_product_feature FOREIGN KEY (product_feature_id) REFERENCES           pr_product_features (product_features_id) ON DELETE CASCADE ON UPDATE CASCADE,
   constraint pr_product_feature_applicability_ibfk_1 FOREIGN KEY (product_id)  REFERENCES product (Product_id)

This design seems good to me (I understood this design from some internet materials), but I am facing some issues.

Consider the following table values,

Brand Color Size Unit Dimension Feature Product Product Feature enter image description here

In front end we are confused because,

  1. For a size there are option for multiple colors and brands.
  2. For a color there are option for multiple sizes and brands.

Developers do understand the logic but it is confusing to write sql query to insert values to the pr_product_feature_applicability table. Since while we choosing multiple colors there are different possible ids for a single size, how to insert multiple color id's in a pr_product_feature_applicability table with a single insert statement.

closed as too broad by LowlyDBA, RDFozz, mustaccio, McNets, hot2use Oct 11 '17 at 11:03

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • why dont you want to use eav ? – Baby in Database Sep 22 '16 at 10:08
  • This question is years old. Any objection to closing it @rathishDBA? – Evan Carroll Oct 10 '17 at 21:07
  • I implemented it with some work around. No objection to close this question. If I get an answer at SQL level, it will be great!! Thanks. – rathishDBA Oct 11 '17 at 6:20

SQL is not my strong point, but this was an interesting question with no answers for such a long period of time.

I would use HTML arrays to set the extra detail:

<input type="text" name="model"> //say AA-1501
<input type="text" name="color[]"> //say red
<input type="text" name="color[]"> //say green
<input type="text" name="color[]"> //say black

<input type="text" name="size[]"> //say 15
<input type="text" name="size[]"> //say 16
<input type="text" name="size[]"> //say 17

On the back end in PHP this will come across as:

$model = 'AA-1501';
$color = array(red,green,black);
$size  = array(15,16,17);

Then use your SQL to insert the correct data into the fields you want.

PHP implode, array_walk will help you a bit :)

I understand this won't help you now, but I'm sure someone might find it useful.

  • dont you suggest eav ? – Baby in Database Sep 22 '16 at 10:08
  • what even makes you think he's using PHP? – Evan Carroll Oct 10 '17 at 21:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.