bitt3n
asked on
did I screw up this database structure?
I currently have two tables, products(product_id,title,
Products can be either male-specific, female-specific, or unisex. Almost no products are unisex. Therefore almost all products have one row in the sex table, while a few have two (one for each sex).
hit_counter counts the number of times users who are viewing a list of products specific to a given sex have clicked on the product in question. Thus if a given product is unisex, it will have two rows in the sex table (one for each sex), and the hit counter for these two rows will be different (since one counts the number of hits the product gets in the section of women's products, while the other counts the number of hits in the men's section.)
date indicates the date the product was added to the section of the relevant sex (eg, the date it was added to the women's section, which might be different from the date it was added to the men's section if the product is unisex)
Now I am having second thoughts whether this is the smartest way to set up my tables. Since almost no products are unisex, I figured a separate sex table was better than extra columns in the products table with irrelevant entries in almost every row (for example, male date and hit counter for a female specific product that does not appear in the men's section), which seemed like bad database design. Removing the sex table would certainly simplify my queries, and would get rid of a table with 500K rows.
Thinking about this again now it seems like I could just use two columns for the hit counters, and if one of the sexes is irrelevant, use NULL for that counter, which means I could get rid of the sex table by adding five columns to the products table (these being sex, male_hit_counter, female_hit_counter, date_added_to_male_section
Products can be either male-specific, female-specific, or unisex. Almost no products are unisex. Therefore almost all products have one row in the sex table, while a few have two (one for each sex).
hit_counter counts the number of times users who are viewing a list of products specific to a given sex have clicked on the product in question. Thus if a given product is unisex, it will have two rows in the sex table (one for each sex), and the hit counter for these two rows will be different (since one counts the number of hits the product gets in the section of women's products, while the other counts the number of hits in the men's section.)
date indicates the date the product was added to the section of the relevant sex (eg, the date it was added to the women's section, which might be different from the date it was added to the men's section if the product is unisex)
Now I am having second thoughts whether this is the smartest way to set up my tables. Since almost no products are unisex, I figured a separate sex table was better than extra columns in the products table with irrelevant entries in almost every row (for example, male date and hit counter for a female specific product that does not appear in the men's section), which seemed like bad database design. Removing the sex table would certainly simplify my queries, and would get rid of a table with 500K rows.
Thinking about this again now it seems like I could just use two columns for the hit counters, and if one of the sexes is irrelevant, use NULL for that counter, which means I could get rid of the sex table by adding five columns to the products table (these being sex, male_hit_counter, female_hit_counter, date_added_to_male_section
ASKER
I don't actually have a unisex sex. If a product is unisex, it has 2 rows in the sex table, one for each sex. (Very few products have this.) If I were to combine the sex and products tables I would either have two boolean columns for male and female (with unisex being TRUE for both columns), or I would have one column for sex with the sexes being numbered 0-2 (male, female, unisex).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your feedback, and sorry for the delay in response. I'm still a bit confused. It appears you are suggesting that I replace the sex (or as you indicate, gender) table, with a sections table, which includes a section_id and section_name in addition to the gender.
I'm not sure of the logic for putting the gender information is in the sections table. It seems like in most cases, gender is a property of the individual product, rather than of the section. Most sections (eg, shoes, shirts, shorts) have both male and female products, so almost every section in the sections table is going to require a row for each gender. On the other hand, most products are limited to one gender.
Would it not be simpler to have separate gender and sections columns in the products table (ie, use a gender column with values 0,1,2 for male, female, unisex, and a second section column with section_id)? It seems your solution will make the tables much larger than they would be were gender and section_id separated in the products table, but maybe I am misunderstanding.
I'm not sure of the logic for putting the gender information is in the sections table. It seems like in most cases, gender is a property of the individual product, rather than of the section. Most sections (eg, shoes, shirts, shorts) have both male and female products, so almost every section in the sections table is going to require a row for each gender. On the other hand, most products are limited to one gender.
Would it not be simpler to have separate gender and sections columns in the products table (ie, use a gender column with values 0,1,2 for male, female, unisex, and a second section column with section_id)? It seems your solution will make the tables much larger than they would be were gender and section_id separated in the products table, but maybe I am misunderstanding.
Hello bitt3n!
Oh, you are not misunderstanding, I just made a mistake in the products example:
product_id,title,brand,pri
1 A_M NIKE $100.00 1
2 A_F NIKE $80.00 2
3 A_M NIKE $100.00 3
(It's a 3 instead of a 2. It was supposed to represent UNISEX, with a section_id of 3 in the sections table)
Also, you are correct: my design will make it tables go larger, but that's only due your requirements, which is having a counter relying on a section based on a product. I can't think of having a counter for EACH product, as you mentioned before, they can be unisex and there's no way to know from which section they came from. Having a separte section for each helps to encapsulate the counters, while using a level based hides some counters when unisex happens in a product that does not have a specific gender.
tl;dr. Stick with your design if there are always both male and female products for a specific set of products. If there are products that have female and unisex, male and unisex, mine can help you to divide the counters and provide a better insight, although the tables size will increase.
Oh, you are not misunderstanding, I just made a mistake in the products example:
product_id,title,brand,pri
1 A_M NIKE $100.00 1
2 A_F NIKE $80.00 2
3 A_M NIKE $100.00 3
(It's a 3 instead of a 2. It was supposed to represent UNISEX, with a section_id of 3 in the sections table)
Also, you are correct: my design will make it tables go larger, but that's only due your requirements, which is having a counter relying on a section based on a product. I can't think of having a counter for EACH product, as you mentioned before, they can be unisex and there's no way to know from which section they came from. Having a separte section for each helps to encapsulate the counters, while using a level based hides some counters when unisex happens in a product that does not have a specific gender.
tl;dr. Stick with your design if there are always both male and female products for a specific set of products. If there are products that have female and unisex, male and unisex, mine can help you to divide the counters and provide a better insight, although the tables size will increase.
Some Pseudocode please:
If Prod_ID exists in Male and If Prod_ID exists in FeMale, then Print "UniSex"
Does that make sense?
HTH,
Kent