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... **


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.

  • my data structure is different than this example.... i just wanted to know ... my table will be normalized if i have ... table like user_outfits... means i have table where will be shirt_id , pant_id and belt_id in the same table... Dec 12 '14 at 5:57
  • Because one of my friend said scenario 1 will be more normalized then scenario 2.... so scenario 2 is more normalized ...? Dec 12 '14 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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