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.
|