[AccessD] Maximum number of columns in a table ?

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




More information about the AccessD mailing list