a tricky situation
Moderator: General Moderators
-
thewebdrivers
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 17, 2007 3:32 pm
- Location: india
- Contact:
a tricky situation
hi all
I am stuck into a tricky situation and i need your help to get out of that.
I am working on a system which has a multicategories system and each article can be assigned to more than one category. For that I am storing all category ids separated by commas against articleid like below:-
artcleid | categoryids
1 | 45,56,78
2 | 67,98,34
I am updating current system which used to have one on one relationship between categories and articles.
Now there are some queries that need updating. one such query is "select * from articles where categoryids < 100"
Now I am quiet confused on how should i update this query so that it fits current db structure. Basically i should check all category ids in categoryids field which have atleast one value which is less than 100.
I hope you understand my problem. any help will be highly appreciated.
Regards
Mike
I am stuck into a tricky situation and i need your help to get out of that.
I am working on a system which has a multicategories system and each article can be assigned to more than one category. For that I am storing all category ids separated by commas against articleid like below:-
artcleid | categoryids
1 | 45,56,78
2 | 67,98,34
I am updating current system which used to have one on one relationship between categories and articles.
Now there are some queries that need updating. one such query is "select * from articles where categoryids < 100"
Now I am quiet confused on how should i update this query so that it fits current db structure. Basically i should check all category ids in categoryids field which have atleast one value which is less than 100.
I hope you understand my problem. any help will be highly appreciated.
Regards
Mike
Do not store your categories like this. You want to have another table for the join with a separate record for each relationship
Articles Table
articleID | articleText | articleTitle
Categories Table
categoryID | categoryName
ArticleCategories Table
articleID | categoryID
So for the examples you posted, you would have this in the ArticleCategories Tabe.
articleID | categoryID
1 | 45
1 | 56
1 | 78
2 | 67
2 | 98
2 | 34
Articles Table
articleID | articleText | articleTitle
Categories Table
categoryID | categoryName
ArticleCategories Table
articleID | categoryID
So for the examples you posted, you would have this in the ArticleCategories Tabe.
articleID | categoryID
1 | 45
1 | 56
1 | 78
2 | 67
2 | 98
2 | 34
-
thewebdrivers
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 17, 2007 3:32 pm
- Location: india
- Contact:
yeah i know, that would have been the best but there are some other restrictions that i cannot use the structure you suggested. Its a very big portal and we do not have time to implement the structure you suggested. Do you have any idea what can we do using existing structure? Can we use any stored procedure for this purpose?
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
But you have the time to code all the manipulations and lookups needed to work with the far less flexible comma separation? Also note that the number of categories one may associate with an article will vary depending on the numeric length of the category ID.
FIELD() or IN() may work, but I can't recall specifically if they would or not in this case. You may need to use a regular expression.
FIELD() or IN() may work, but I can't recall specifically if they would or not in this case. You may need to use a regular expression.
-
thewebdrivers
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 17, 2007 3:32 pm
- Location: india
- Contact:
-
thewebdrivers
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 17, 2007 3:32 pm
- Location: india
- Contact:
-
thewebdrivers
- Forum Commoner
- Posts: 41
- Joined: Fri Aug 17, 2007 3:32 pm
- Location: india
- Contact: