Linking databases - basic design problem

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Linking databases - basic design problem

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Linking databases - basic design problem

Page: [1]


bernieboy31 Posts: 79 Joined: 3/5/2005 From: London, United Kingdom Status: offline	Linking databases - basic design problem - 3/24/2008 4:29:04 Gurus, Let's say I have two .db tables 'Category' and 'Items'. 'Category' is just a text field with a primary key - no limit on number of records. As well as the data fields 'Items' contains a category field that links to the 'Category' table via the 'Category' primary key ID. As a result, it's easy to run a DRW to lookup all 'Items' under a single category e.g. "SELECT * FROM links WHERE Category = " & cat_ID Problem is that I now need some 'Items' records to be applicable to more than one category and need to modify the DRW accordingly. I have been playing around with some test data, getting nowhere and tearing my hair out. Using some form of filtering using 'MOD = 0 ' seems to me the likely way but, for the life of me, I cannot find an easy/straightforward way of doing it. I accept that I will probably need to redesign a bunch of stuff but that's not an issue. Can you kind people give me some ideas on how I can acheive this as the need must have occurred before. TIA Cheers Bernie


rdouglass Posts: 9167 From: Biddeford, ME USA Status: offline	RE: Linking databases - basic design problem - 3/24/2008 8:54:26 There are 2 ways that I can think of right off. 1. Use an intermediate table. Instead of having the item assigned to a category directly in the Items table, make an intermediate table with 2 items; CategoryID and ItemID. Then you could have a 1 to many relationship between Items and the new table. 2. Change the category field in the Items to support multiple items and store the category ID's as a comma delimited string of ID's. Then you could use a method similar to the checkbox stuff I talk about in the Checkbox Tutorial in my Sig line below. Those are 2 ways and I'm sure there are more. That help any? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to bernieboy31)


bernieboy31 Posts: 79 Joined: 3/5/2005 From: London, United Kingdom Status: offline	RE: Linking databases - basic design problem - 3/25/2008 14:24:19 Thanks VM - great advice as usual. I like the idea of an intermediate table for the 'one to many' relationship. I am going to head down that route and probably use an access query within the server side .mdb file to link all three tables - This would make it tons easier to display the resultant data rather than do it all in .asp Doubtless, I will be screaming for more help Cheers Bernie (in reply to rdouglass)