Linking databases - basic design problem (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

bernieboy31 -> 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 -> 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?





bernieboy31 -> 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 [8D]

Cheers
Bernie







Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125