Print Page | Close Window

Multiple category associations

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=20155
Printed Date: 28 March 2026 at 11:46pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Multiple category associations
Posted By: dfrancis
Subject: Multiple category associations
Date Posted: 02 June 2006 at 6:02pm
Database design is an art and I'm still drawing stick figures. Typically I'll design my database in Access then upgrade to SQL before going to production. (This has nothing to do with my question, sorry.)
 
How do you associate multiple selections in a table?
 
I will show the structure here, which is overly confusing and in development, but my question is simpler than the data structure.

 

 
What I am trying to accomplish is to  allow multiple selections from the Categories table.
 
The Categories table will hold a number of commonly used topic categories for blog posts (with the option to add new by the member). I would like to populate a multiple select box so the post can be associated with more than one category.
 
I've done it before but cannot for the life of me remember how and I was hoping someone would be able to assist.

Waddaya think?




Replies:
Posted By: Mart
Date Posted: 02 June 2006 at 9:51pm
You could make a new table called PostCategories (FK = Foreign Key)

+===================+
|          PostCategories          |
+===================+
|  PostID (int) FK                   |
| CatID (int) FK                      |
+===================+

then drop the Category column.

EDIT: A better tool for database design would be SQL Server 2005 Management Studio Express (kind of a mouthful). You can get it free with SQL Server 2005 Express from MS


Posted By: michael
Date Posted: 02 June 2006 at 10:00pm
You would have three tables, posts, categories and PostsInCategories. The exiting 2 would remain unchanged except you would drop the relationship between them. the PostsInCategories would have two fields (PostID, CatID). Create a Rel from CatID to CatID and PostID to PostID. Then the data can look something like

PostID    CatID
5        1
12        1
12        2
12        4
13        6

So essentially each post can have unlimited Categories.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker


Posted By: michael
Date Posted: 02 June 2006 at 10:01pm
Yeah I write slower... :)

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker


Posted By: dfrancis
Date Posted: 02 June 2006 at 10:19pm
This is great... thanks guys.
 
I kind of thought this was the case but wanted to present the query without the influence of my assumptions.
 
Thanks for the tip on SQL express. I've screwed up my computer so bad with all the betas that I have to clean it off before I can look at something like that. I already run SQL express server AND SQL 2000 full version and the two are not happy with each other.
 
June is all about setting up some old computers as servers so I can stop screwing up my work station.
 
Thanks again... I will use the lookup table idea.



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net