Arthur Fuller
artful at rogers.com
Fri Feb 7 15:38:01 CST 2003
A junction table effects a many2many relationship. Students and Courses; many of each other. So you create a junction table tblCourseStudents consisting of an ANPK, a CourseID and a StudentID, with the latter pair indexed unique so you can't enroll a kid twice in Shotokan Karate 101. Many kids take many courses. Many courses contain many kids. The junction table gives you all the data in either direction. -----Original Message----- From: accessd-admin at databaseadvisors.com [mailto:accessd-admin at databaseadvisors.com] On Behalf Of Oleg_123 at xuppa.com Sent: February 7, 2003 2:51 PM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Maximum number of columns in a table ? I know that right way usually takes longer to develop :)) Whats a junction table ? I remember when I took SQL class, professor said never to have a table that contains data gotten from other table through math manipulations.. I was thinking about having a table with coumns 1,2,3,4,5,6,7,8,9 ---> 78 And then a a query with columns 2,3, 4-6, 7-12, etc. I'll read the article that u send again > I dunno most Access DBA's usually do a minimum of 3 peoples work... for > some reason it seems to traditionally move this way, it's probably due > to the fact that Access has become such a memory, network traffic, and > HDD footprint HOG, that as developers we're forced to deal with many > issues that other developers don't. Either that or it's just me, and my > fascination w/ computers ;o). > > In the end "You must DO what the boss wants". That doesn't mean you > have to sacrifice the data model or your principles. Remember exactly > what they've asked you... they probably asked, "we need more fields on > this form to input more data". That is not the same as more fields in > your table. Being in the habit of maintaining my own apps, I want to > always improve both my work style and development cycle to make > maintenance a breeze. Of those things I've learned that the right way > usually takes longer to develop, but is frequently much much easier to > maintain. Adding additional "virtual" fields to screens and tables is a > breeze when the system is developed correctly. In your case it seems > that what you are after is a junction table w/ a temp table. The > junction table will store all the data you need, month 1,2,3, etc... but > the temp table would fill in the blanks. Such that If data is entered > for Quote MS, for months 1, 3, 7... then that data would be stored in > the junction table to something that resembled this: > > PKID > QuoteID > DateID <--represents a number 1-12 or whatever your breakdown is. > Value > > the temp table would auto-generate all the missing dates/months buy > comparing with a base table that that includes all these months > something similar to the following setup.. > > PKID > DateID > > Buy running an unmatched INSERT query you can quickly add fields to a > temp table w/ all the real data and the virtual fields for binding to > the form or report. Are these a lot of steps? YES, are they worth it?, > YES. You are saving space in your database by not storing the > additional fields that you don't need, but at the same time now have the > flexibility to add additional fields by simply visiting your MonthBase > table so you can add Months from 0 - Infinity. > > HTHs, > -Francisco ----------------------------------------- Send a Xuppa Valentine to Your Sweetheart today! http://www.xuppa.com/greet/ _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com