| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
|
|
Anonymous
Starting Member
0 Posts |
 Posted - 12/19/2000 : 16:38:10
|
| Url & Category Ok, I think I got what you said. I have a related question. I have two tables. First TB_Category which stores information about categories. The second one is TB_Url which stores information about adresses. TB_Url has a column called Cat_ID. My problem starts here. Each url can be an element of 2 or more categories. Instead of keeping a row for each category for the same Url, I decided to make Cat_ID a varchar, and insert the categories as ',12,'. So when I have multiple categories it goes like ',12,13,19,59,'. And in my search it goes like 'where cat_ID like ',12,'. But this method is hard to update. Which is better? Having multiple rows for the same Url (this might involve keeping the Url info in a different table, and just keeping UrlID and CatID in a table and making a triple join) or the string search I created. Which one is more adventagous when numbers and DB size grows large.
I'd be glad if you reply. Thanks
Ascii255
|
 |
|
|
seabarak
Starting Member
1 Posts |
Posted - 01/12/2002 : 16:30:18
|
Dear Ascii255,
What you should do is create a third table, called "TB_URLCategories", for example, in which you will store a URL_ID and Cat_ID for each category a URL belongs to.
The basic idea is that you should not be duplicating any data ( category names or URLs), and you should definately not be storing IDs in comma delimited strings.
Instead, by having a third table, when a URL needs to be entered into more than one category, the insert process would do the following:
1. Insert the URL into the TB_Url table (if it doesn't already exist in there) and get the URL's new URL_ID.
2. For each category the URL belongs to, insert the URL_ID and Cat_ID into the third table.
Then, when you want to find all URLs in Category X you would simply:
SELECT [Fields you want to select go here] FROM TB_Url
WHERE URL_ID IN (SELECT URL_ID FROM TB_URLCategories WHERE Cat_ID= [Cat_ID goes here])
Hope this helps.
Best regards,
Barak
quote:
Url & Category <P>Ok, I think I got what you said. I have a related question. I have two tables. First TB_Category which stores information about categories. The second one is TB_Url which stores information about adresses. TB_Url has a column called Cat_ID. My problem starts here. Each url can be an element of 2 or more categories. Instead of keeping a row for each category for the same Url, I decided to make Cat_ID a varchar, and insert the categories as ',12,'. So when I have multiple categories it goes like ',12,13,19,59,'. And in my search it goes like 'where cat_ID like ',12,'. But this method is hard to update. Which is better? Having multiple rows for the same Url (this might involve keeping the Url info in a different table, and just keeping UrlID and CatID in a table and making a triple join) or the string search I created. Which one is more adventagous when numbers and DB size grows large.
I'd be glad if you reply. Thanks
Ascii255
|
|
|
| |
Topic |
|
|
|
Article: Database Server Sizing
