Steve Erbach
erbachs at gmail.com
Mon Apr 4 14:59:34 CDT 2005
Jim, I think we're on the right track here. You're talking about many-to-many linking tables. Thanks for detailing this method. At least YOU took me seriously. Rocky and Gustav just came up with "take time to smell the roses!" Sheesh! Steve Erbach On Apr 3, 2005 9:56 PM, Jim Hewson <JHewson at karta.com> wrote: > Steve, > What you need are junction tables. Tables that hold PKs from different tables. > I've done this for several different databases. > I'm currently working on one for pastoral musicians to keep track of their music. > Songs, composers (with multiple songs), who arranged the piece (could be composer or someone else), instruments (single or multiple), copyright holders and addresses. A song could be sheet, book, cd, octavo or combination. A song could be used for one or more liturgical season (e.g. Tridiuum and Easter). I have seen two songs with the same title, different composers. What is the vocal arrangement (choir, duet, quartet, etc.) if any. > > In your sample below, you had a closet with several different collections. Let's use the family room, hall closet and guest room. PK for these three rooms are 1, 2, and respectively. > The second collection is for videos - Video 1, Video 2 and Video 3. > Third collection is for Music CDs - CD 1, CD 2, and CD3. > > I typically keep junction tables to two columns (one for each table) to allow a multiple combinations between the them. > > By using a junction table the PKs of four tables become FKs. > > RmID MusicID > 1 2 > 2 1 > 3 3 > 1 1 > > RmID VidioID > 1 1 > 2 3 > 3 2 > 2 1 > > RmID CDID > 1 2 > 2 1 > 3 3 > 1 1 > > Using junction tables, you can create a reports. > For example, you want a report by room of everything that's in the rooms. > Using the Junction table you can pull the room with ID 1 into one report. > The common thread in your example are the rooms. > > HTH > Jim >