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?
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With