Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple boolean attributes vs. New table

I am designing a relationship between mall and brands available at the mall.

The number of brands is fixed. However, it may increase in future. Now, a mall may feature some/all brands.

My question is:

Should I add the different brands as boolean attributes to the mall table like this:

mall
----------------------------------------------------------------------------
  mall_id  | mall_name | peter_england |   turle   |   adidas   |   puma   |
---------------------------------------------------------------------------- 
      1         xyz         false          true         true        false
      2         abc         true           false        true        true
----------------------------------------------------------------------------

OR

Create a N:M relationship between mall and a new brand table like this:

mall                             mall_brand                  brand
-------------------------        ---------------------       --------------------------
  mall_id  | mall_name |          mall_id  | brand_id         brand_id |   brand_name
-------------------------        ---------------------       --------------------------
      1         xyz                  1          2                 1      peter_england
      2         abc                  1          3                 2      turtle
-------------------------            2          1                 3      adidas
                                     2          3                 4      puma
                                     2          4            --------------------------
                                 ---------------------

I personally find the second one more flexible since it only stores true values. Besides, it also gives me the provision to add extra attributes to individual brands in future. Also, adding new brands is neat.

EDIT:

Also consider a situation where all of these brands exist for every mall and instead of storing boolean values, we're storing float values for average number of garments sold for each brand per day like this:

mall
----------------------------------------------------------------------------
  mall_id  | mall_name | peter_england |   turle   |   adidas   |   puma   |
---------------------------------------------------------------------------- 
      1         xyz          10.4          21.3         13.7        7.6
      2         abc          10.7          25.5         11.4        8.1
----------------------------------------------------------------------------

In this case should I store them as attributes, or create new tables similarly like before?

like image 345
Nikunj Madhogaria Avatar asked Oct 17 '25 00:10

Nikunj Madhogaria


1 Answers

Updating answer after some misconception based on the discussion with @Gerrat

Of course the Second way is better.

The first table is in First Normal Form since none of its domains (field values) have any sets as elements (columns with multi-values). But the problem is with the repeated columns (brands that are repeating as columns in the table just to be marked in the mall), such a design is often considered an anti-pattern however because it constrains the table to a predetermined fixed number of values and because it forces queries and other business logic to be repeated for each of the columns. In other words it violates the "DRY" principle of design.

Always try to make your database, easy to maintain and normalized as much as possible.

References :

https://en.wikipedia.org/wiki/Database_normalization

https://en.wikipedia.org/wiki/First_normal_form

https://en.wikipedia.org/wiki/Second_normal_form

https://en.wikipedia.org/wiki/Third_normal_form

https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form

Normalization: What does "repeating groups" mean?

like image 167
KAD Avatar answered Oct 19 '25 14:10

KAD