how to separate the table

[awash]

how to separate the table

i am now establish a web site selling products.

1. i use SQL Server.
2. we have ten over ten thousand itmes for sale.
3. the web site is presented in three languages.
4. some items have more than one catagory code. for example, a book can be idend
ified as a novel as well as a foreign literature, therefore, it has two catagory
codes, so people can look for it from two different catagories.
5. although the book has more than one catagory code, it has only ONE product co
de. therefore, PRODUCT tables should contain at alease two tables.


there are two choices to build the PRODUCT tables:

first choice:
building five tables

table1:
field name / date type / length
productid char 9 (the product code)
bigcatid char 2 (the big catagory code)
midcatid char 2 (the middle catagory code)
smallcatid char 2 (the small catagory code)

table2:
productid char 9 (the product code)
A_name char 30 (the name of the item in language A)
A_desc Varchar 300 (description of the item in language A)
A_bonuspoint numeric 9 (the bounus points of the item in language A area)
A_retail_price numeric 9 (retail price in language A area)
A_member_price numeric 9 (member price in language A area)
A_discount numeric 9 (the discount of the item in language A area)
A_delivery_rate 9 (how many such item are sold)
A_spec Varchar 300 (specification of the item)
A_remark Varchar 100 (reserve for furture use)


table3:
productid char 9 (the product code)
B_name char 30 (the name of the item in language B)
B_desc Varchar 300 (description of the item in language B)
B_bonuspoint numeric 9 (the bounus points of the item in language B area)
B_retail_price numeric 9 (retail price in language B area)
B_member_price numeric 9 (member price in language B area)
B_discount numeric 9 (the discount of the item in language B area)
B_delivery_rate 9 (how many such item are sold)
B_spec Varchar 300 (specification of the item)
B_remark Varchar 100 (reserve for furture use)


table4:
productid char 9 (the product code)
C_name char 30 (the name of the item in language C)
C_desc Varchar 300 (description of the item in language C)
C_bonuspoint numeric 9 (the bounus points of the item in language C area)
C_retail_price numeric 9 (retail price in language C area)
C_member_price numeric 9 (member price in language C area)
C_discount numeric 9 (the discount of the item in language C area)
C_delivery_rate 9 (how many such item are sold)
C_spec Varchar 300 (specification of the item)
C_remark Varchar 100 (reserve for furture use)


table5:
productid char 9 (the product code)
photobig_path Varchar 30 (the path of the big picture)
photosmall_path Varchar 30 (the path of the small picture)
quantity numeric 9 (instore quantity of the item)
in_stock_flag boolean (to judge whether there is such item in stock)
supplierinfo Varchar 15 (the code given by the supplier, for example, "ISBN 7-15
-08061-5" the code of a book)







second choice:
building two tables

table1:
field name / date type / length
productid char 9 (the product code)
bigcatid char 2 (the big catagory code)
midcatid char 2 (the middle catagory code)
smallcatid char 2 (the small catagory code)

table2:
productid char 9 (the product code)
A_name char 30 (the name of the item in language A)
A_desc Varchar 300 (description of the item in language A)
A_bonuspoint numeric 9 (the bounus points of the item in language A area)
A_retail_price numeric 9 (retail price in language A area)
A_member_price numeric 9 (member price in language A area)
A_discount numeric 9 (the discount of the item in language A area)
A_delivery_rate 9 (how many such item are sold)
A_spec Varchar 300 (specification of the item)
A_remark Varchar 100 (reserve for furture use)
B_name char 30 (the name of the item in language B)
B_desc Varchar 300 (description of the item in language B)
B_bonuspoint numeric 9 (the bounus points of the item in language B area)
B_retail_price numeric 9 (retail price in language B area)
B_member_price numeric 9 (member price in language B area)
B_discount numeric 9 (the discount of the item in language B area)
B_delivery_rate 9 (how many such item are sold)
B_spec Varchar 300 (specification of the item)
B_remark Varchar 100 (reserve for furture use)
C_name char 30 (the name of the item in language C)
C_desc Varchar 300 (description of the item in language C)
C_bonuspoint numeric 9 (the bounus points of the item in language C area)
C_retail_price numeric 9 (retail price in language C area)
C_member_price numeric 9 (member price in language C area)
C_discount numeric 9 (the discount of the item in language C area)
C_delivery_rate 9 (how many such item are sold)
C_spec Varchar 300 (specification of the item)
C_remark Varchar 100 (reserve for furture use)
productid char 9 (the product code)
photobig_path Varchar 30 (the path of the big picture)
photosmall_path Varchar 30 (the path of the small picture)
quantity numeric 9 (instore quantity of the item)
in_stock_flag boolean (to judge whether there is such item in stock)
supplierinfo Varchar 15 (the code given by the supplier, for example, "ISBN 7-15
-08061-5" the code of a book)



If use the first choice (five tables one), to show products, we should at first
search table1, and then search one of the table of table2, table3 and table4, and
then search table5.
If use the second choice (two tables one), to show products, we should at first
search table1, and then search table2.
The difference between two choice is that:
1. if use the first choice (five tables one), we should search three times, whic
h is one more time than the second choice. If there are many items in the tables,
people may wait for a long time, since we need more time for searching.
2. if use the seconde choice (two tables one), i use ASP's RecordSet, since ther
e are many fields in the table2, does it need more hardware resource (such as RAM)?

which choice is better? or there are other choices better than the two.

[Axiong]

2