database design help

[mark_me]

Hi, I need some experienced advise on a situation that i am facing. Please provide help. I have a products excel sheet. currently 20,000 products but they keep on increasing. i am asked to develop the sql tables. the products can be divided into 2 types. One type have about 30% rows whereas other will have 70%. In database implementation, i can have one table with type field which separte the 2 types also I can have 2 different tables for both types. My question is : Between the two, for searching purposes which proves the best one. ie. on average 1 table search provides better performance or 2 tables provide better performance. thankyou

[David Mujica]

Hard to say whether you will get better performance with searching 1 large table or 2 smaller tables without knowing the details, but with the number of records you are talking about (less than 100,000) you will be amazed at how fast SQL server can return results from a query compared to searching in Excel. My suggestion is to store the data in one table, with a "type" idicator. Creating the proper indexes will also improve performance depending on the nature of the query. Question: Do all of the columns for each product "type" apply ? Another way of asking this question is that if you created 2 tables; one for each product type, would they have the same columns ? If so, then for sure, go with 1 table and an idicator field. BTW: What type of application will be accessing this data ? ASP.NET or a Windows client application ?

[mark_me]

Thankyou for responding Do all of the columns for each product "type" apply? theoretically , both should have same detail. but does the number of columns matter. What i mean is that if product has 12 columns and i split it into two tables. 1. productname 2. productdetail would select statement make a difference? to my knowledge, its the same thing. the application would be used by asp.net. Also if u don't mind, it would be nice if u mention whether its wise to use mysql db or sql server db. is sql server n asp.net better, or is sql server or php better, or asp.net n mysql or other way round. Thanks again.

[Robin_Roy]

Better go with a single table with a bit field for product type. Maintaining 2 different table for the same identity is not a good dB design practice. The performance wont be an issue with the size of record that you are saying. Implement indexes on the columns that you will use in the query conditions. Now regarding the selection of database, for ASP.Net, better go for MS-SQL Server. As .Net framework provides native driver for MS-SQL Server. This is the managed driver and performance is better. However, the hosting cost for MS-SQL Server is higher, if budget is a constraint, you can go for mySQL.

[Mycroft Holmes]

You actually need 2 tables Product ProductType for when they add a 3rd product type. As said by others create the product table with all the details and the a foreign key to the product type table. 100k rows is nothing to a database, add 2-3 zeros and you MAY have performance issues to address.

Never underestimate the power of human stupidity RAH

[mark_me]

thankyou

[mark_me]

thankyou