Good Normalization Practices for MySQL Database Design

database-designMySQLnormalization

ITS JUST FAKE TABLE DATA TO SIMPLIFY MY PROBLEM

I have tables ,, Users , shirts, pants, belts …. each will have some data…

so by some research i have made some scenarios of links between the tables so what would be the Optimized and very good ..

Scenario 1

I will have tables

  1. user_shirts ( user_shirt_id (PK), user_id(FK), shirt_id(FK))
  2. user_shirts_pants (user_shirts_pants_id (PK), user_shirt_id (Fk), pant_id(FK))
  3. user_shirts_pants_belts (user_shirts_pants_belts_id(PK) , user_shirts_pants_id (Fk), belt_id (Fk))

If there is

users(1)
Shirts ( A, B)
pants (a,b,c)
belts(I,II)

there will be 1 row in users table…
there will be 2 rows in users_shirts table…
there will be 6 rows in user_shirts_pants table…
there will be 12 rows in user_shirts_pants_belts table….

then this will have data

Scenario 2

I will have tables

  1. user_shirts_pants_belts (user_shirts_pants_belts_id(PK) , user_id(FK), shirts_id (Fk), pant_id(FK), belt_id (Fk))

users(1)
Shirts ( A, B)
pants (a,b,c)
belts(I,II)

  • there is 1 row in users table
    -there will be 12 rows in user_shirts_pants_belts table….

**So which scenario is good.. and in normalization form… i should adopt whi9ch scenarion… if any better scenario then this then suggest me…? So i can make more and more relational combination and i easily update and delete them… **

Best Answer

What is it we are trying to get to here? Do you want to list out all the outfits (combinations of shirts, belts, and pants) a user can wear? Or just what they have worn for a particular day? Sorry, I'm not following the needs for the three relational tables (user_shirts, user_shirts_pants, user_shirts_pants_belts).

I would just have one table for each item (with pkid and description of item), then a table that has a separate foreign key to those item tables:

users (id int, name varchar(100))
shirts (id int, desc varchar(100))
pants (id int, desc varchar(100))
belts (id int, desc varchar(100))
user_outfits (id int, user_id int, shirt_id int, pant_id int, belt_id int, dt date)

Then you could enter any item they wore or null if they didn't wear that item.

Related Question