shapper
3/28/2007 12:30:00 PM
Hello,
I am creating a simple blog system using SQL 2005.
I have a Blog table:
[BlogId] > PostId (PK), BlogTitle, ...
And a Posts table
[Posts] > PostId (PK), BlogId (FK), PostContent, PostLabels, ...
PostLabels would have the following format:
Label1,Label2,Label3, etc ...
I will need to perform 3 actions:
1. Get all posts in blog
2. Get all labels in a post
3. Get all unique existing labels in all posts in a blog and make a
list.
I am not sure if my approach of using a simple labels column in my
Posts table is a good idea.
So my other idea would be to add two more tables:
[BlogLabels] > BlogLabelId (PK), BlogId (FK), LabelName ...
[LabelsInPosts] > BlogLabelId (PK), PostId (PK)
So my idea is:
1. When creating a post one of the parameters would be a comma
separating string with all labels for the post.
Inside SQL Procedure I will need to loop through each label and
check if it exists in BlogLabels. If not then I added it.
For each label I add a records in LabelsInPosts.
How to create this loop? Am I thinking this right?
2. To get a list of all labels in a blog I would need to go to
BlogLabels and get all labels which are related with posts in
LabelsInPosts. Those posts must be only the ones that are related
with my given BlogId.
Grrr, this is getting really confusing for me.
Is this possible to to? How?
Please, give me some advice about all this.
Thanks,
Miguel