Eloquent relations for product features in a normalized database?

Viewed 69

I've looked through dozens and dozens of Eloquent questions, however the examples are usually simple to grasp. E.g. a User has one Phone or a Category has many posts.

I'm trying to store the product features and build relations between them while keeping the database as closed to normalized as possible, otherwise it'd be easy to just store each combination and its data in a single row.

Consider a product, it can have many features ( Material, Size, Weight, Color etc. ). Each feature assigned to a product can only have one value, for example, the Material is Wood.

To keep away duplicate entries, I'm keeping the Features and Values in separate tables. Because for example Wood can be used both by Material and Texture.

Here's my DB structure:

feature
  - id
  - name ( E.g. Material )

feature_value
  - id
  - value ( E.g. Wood, must be unique )

feature_value_combination
  - id
  - id_feature
  - id_value

product_feature
  - id
  - id_product
  - id_combination

This way I can also group them and prevent duplicates. But I'm having a hard time understanding the relations between these, because a lot of relations can work. My questions are:

  • How many Eloquent do I need? Do combinations need their own Eloquent?
  • What are the relations here? Because it makes sense that a feature value belongs to a combination, but also have a combination, while belonging to a feature name and a product, and also a product can have a feature, a value, a combination. Everything seems mixed up here.

Any help toward clarifying this is appreciated.

Update

I managed to setup the basic relations, but at the cost of creating 5 models, even for pivot tables.

Models:

  • M1) Product
  • M2) FeatureName
  • M3) FeatureValue
  • M4) FeatureNameValue
  • M5) ProductFeatureNameValueRelation

Now I can fetch a product and its feature data using this:

Product
    ::where( 'id', $id )
    ->with( 'getM5data.getM4data.getM3data')
    ->get();

I used short names to make it easier to read. Each of those methods fetch the required data using a relation. However, this results in 5 queries, which is an abomination:

select * from `products` where `id` = 1 
select * from `product_feature_relations` where `product_feature_relations`.`id_product` in (1)  
select * from `product_feature_name_value` where `product_feature_name_value`.`id` in (2, 3)  
select * from `product_feature_values` where `product_feature_values`.`id` in (4, 5)  
select * from `product_feature_names` where `product_feature_names`.`id` in (6, 7)  

This can be done easily using inner joins and 2 queries. I'm not sure how to convert the above to this.

0 Answers
Related