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 |
|
redbrad0
Posting Yak Master
176 Posts |
 Posted - 2006-01-05 : 05:36:41
|
I have a very large database which only has approx 20% of the data in it. I am starting to think that I might have things wrong in the database design so I will try by just asking some general questions.To make things simple I have one table. When I will make a basic outline below.Table [Keywords] ID int Keyword nvarchar(50) SubKeywordID int MainKeyword bit SubKeyword bit Basically there is three types of keywords1. Main Keyword2. Sub Keywords (approx 75 assigned to Main Keyword)3. Additional Keywords (approx 75 assigned to each Sub Keyword and Main Keyword)Sample Data StructureID Keyword SubKeywordID MainKeyword SubKeyword-- ------------ ------------- ----------- ----------1 baby 0 1 02 baby toys 1 0 14 babrbie 2 0 05 legos 2 0 03 baby clothes 1 0 16 gap 3 0 0 There is currently 388,105 Main & Sub Keywords in the table and 11,107,617 Additional Keywords. Again this is only 20% of the records that will be inserted into this table. I am starting to think I should move the Additional Keywords to another table as leaving them in the Keywords table will make the table approx have 57,478,665 records which the Additional Keywords are hardly used.Quality NT Web Hosting & Design |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-01-05 : 05:48:39
|
you'd probably be better of without tree structure here.with simple parent-child table structure and many to many relationships if you need them.Go with the flow & have fun! Else fight the flow  |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-05 : 06:00:43
|
| So if I search for "babrbie" (I've kept your spelling in case it isn't the pink doll I think it is!) I will get any products which explicitly have the "babrbie" keyword ranked first, then anything with the keyword "baby toys" followed by anything with just "baby" ?Seems like a good plan.I expect the query is pretty horrid though ... as it looks like it will have to be recursive. That's the thing I would work on, and check how fast the run-time is, and then circle back to the table design if that proves to be a dog!What happens if I search for "clothes baby" - will that equate to "baby clothes" ??What about "babies clothes"?We have a synonyms table which is used for that (plus common misspellings that we've seen used over the years), but our keywords are all single-words, and we rank according to how many of the words match. (Our Link table between Keyword and Product also has a Weight value - so for a given product "babrbie" may rank 10, "toys" 5 and "baby" 1, for a non-babrbie similar product "babrbie" may only rank 1 - thus still be found in the search, but lower down than any actual babrbie productsKristen |
|
|
|
redbrad0
Posting Yak Master
176 Posts |
Posted - 2006-01-05 : 06:05:27
|
| Thanks for the reply. One thing I just noticed I forgot to mention is there is 4 companys that use this table to store the data. Because the number of company's will never change I thought about splitting up each company to have their own set of Keyword tables so it would help reduce the records it has to search thru to find the data. I am sure this would be a bad idea by having this many tables that are the exact same but with this many records does it make it ok?Quality NT Web Hosting & Design |
|
|
|
redbrad0
Posting Yak Master
176 Posts |
Posted - 2006-01-05 : 06:10:30
|
| Kristen,Sorry late night who I did spell it wrong. Nothing is every searched for the keyword but we give it the main ID and then it returns all the sub keywords or we give it a sub keyword ID and it will return any additional keywords that are about that keyword. I am not sure if that helps any but we never do a %search% on the database.Quality NT Web Hosting & Design |
|
|
|
|
|
|
|
|
Database Design - Best Options