Charlotte Foust
cfoust at infostatsystems.com
Thu Sep 27 16:07:29 CDT 2007
Depends on the level of normalization. Star schemas are normalized but make use of dimension tables that contain the possible values for a query/filter on the field to which they're joined, like every date in a period, for instance. You don't bother with a rollup table, since the data can be rolled up quickly and simply by running a query. And you're asking about Normal in THIS list??? ROFTL Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim Sent: Thursday, September 27, 2007 1:56 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Mucking around But is it normalized? Seems to me that fldType has the same problems as described in the list box article. As a practical matter I would NOT create separate tables since different rollups are needed all the time and the programmers would poison my coffee if I proposed separate tables. I have already lost too many tasters that way. Along the same vein you COULD add a type field to the general ledger balance file to represent plan, actual or statistics but I would never combine those disimilar types of data. So the line beween correct normalization and practicality often blurs for me. I think there is some grey and judgement in creating truly normalized table schema. After all who can truly say what is Normal? :-) Jim Hale -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Thursday, September 27, 2007 2:21 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Mucking around You're talking about creating a star schema, which is commonly used for this purpose in data warehouses. Charlotte Foust -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim Sent: Thursday, September 27, 2007 11:34 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Mucking around I agree on lookup tables but how about "rollup" tables? Here is an example I happen to be working on at the moment. Assume a table with general ledger amounts. fldAcctNumber fldAcctdescription fldMonth fldYear fldCompany fldDept fldAmount. All the accts for a given month,year and company create the trial balance. Now suppose you don't want to see every account in your report but would prefer summary accounts, ie "Sales" is comprised of Accounts "product sales","labor Sales" ,"Service Sales",etc., "General and administrative expense" is comprised of "salaries", "travel and entertainment", "rent", etc. You certainly wouldn't just tack a rollup field onto the general ledger balance file but would create a file with fldAcctNumber fldSummaryAcctNumber fldSummaryAcctdescription. Join this table to the GL balance file on fldAcctnumber, create a groupby query and voila, you have a summary trial balance. This works so well you realize that many other rollups exist, ie you might want to create a "tax books" rollup or "cashflow" rollup all using the fldAcctNumber fldSummaryAcctNumber fldSummaryAcctdescription structure to add together different GL accounts together to produce the desired result. You can create separate tables for each rollup structure or add a type code to the one rollup table and filter based on the desired rollup required for a particular report. Which choice is properly normalized? Which is most practical? Jim Hale -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Wednesday, September 26, 2007 4:52 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] Mucking around Hi John Thanks. He is my man, I've always handled "lookup" tables as any other table. /gustav >>> jwcolby at colbyconsulting.com 26-09-2007 11:25 >>> I found an interesting article on SQL Server central. The subject has been discussed in these hallowed halls so I thought I'd throw this out. http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/146 4/ John W. Colby Colby Consulting www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com *********************************************************************** The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer. As a recipient of this email, you are responsible for screening its contents and the contents of any attachments for the presence of viruses. No liability is accepted for any damages caused by any virus transmitted by this email. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com