Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mani
Starting Member
2 Posts |
 Posted - 2005-07-06 : 22:31:09
|
| I have the following design.Group TableStock Group Id(tinyint) PK, Stock Group Name varchar(30) not null unique, and other few columns-Maximum 10 rowsCategory TableGroup Id tinyint, Category Id smallint (composite pk), Category Name vatrchar(30) and other few colums-maximum-200 rowsSub Category Tablegroup id tinyint, category smallint, subcategory id smallint (composite key), sub category name, and other few colums-maximum -1000 rowsproduct tableproduct id int,productname, group id,category id, subcategory id and other columns-maximum 20,000 rows90%of the time when I query product details i need group name , category name, sub category name along with it not any other details of other tablesSO i have to join four tables. Although keys are very small, i was wondering woudl it be good idea to do away with ids and have names as pk so that when i need produt details i always get stock group name, category name and sub actegory name with out having to join four tablesIf I have names as PK, FK in product table will be 90 bytes varying lengthWhat do you think will give me best performance?Regardsmani |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-07-06 : 23:42:13
|
| The only "con" for the natural key in this case (IMO) is stability. An updateable key would require cascading RI..90 bytes is not much to worry about over 20,000 rows...(~1.8Mb vs 100Kb).. 20 million rows is a different sad story...DavidMA front-end is something that tries to violate a back-end. |
 |
|
|
mani
Starting Member
2 Posts |
Posted - 2005-07-07 : 02:05:56
|
| These tables do not get updated very often. I would be having 3 indexes on product table like this to search for products by any combination of stock group, category and sub categorynon clustered index 1: stock group , category ,sub category non clustered index 2: category,sub category non clustered index 3: sub categoryDont you think SQL server traversing 1.8MB of index will take more time thanjust traversing 100 Kb of index and fetching the name from stock group, cat, sub cat tables even though it involves 4 table join.Application will bring large amount of other data too from transaction table . So buffer pool wont be free enough to say performance difference is too small to differentiate between above two methodsIt's a desk top application using MSDE 7 or 2000, BUt very data intensive applicationI want to be very sure about this cause it will affect my entire database design. |
|
|
|
|
|
|
|
|
Use natural key as primary key to avoid join?