Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Multiple category associations
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Multiple category associations
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
dfrancis View Drop Down
Senior Member
Senior Member


Joined: 16 March 2005
Location: United States
Status: Offline
Points: 442 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dfrancis Quote  Post ReplyReply Direct Link To This Post Topic: Multiple category associations
    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?
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post 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


Edited by Mart - 02 June 2006 at 9:53pm
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post 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.
Blog | MPG Tracker
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post Posted: 02 June 2006 at 10:01pm
Yeah I write slower... :)
Blog | MPG Tracker
Back to Top
dfrancis View Drop Down
Senior Member
Senior Member


Joined: 16 March 2005
Location: United States
Status: Offline
Points: 442 		Post Options Post Options   Thanks (0) Thanks(0)   Quote dfrancis Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.