Re: DB design and normalization
From: Michael Malinsky (joeqcpa_at_yahoo.com)
Date: 06/15/04
- Next message: Jay Vinton: "RE: Custom AutoNumber"
- Previous message: Plover Rover: "ActiveX Error"
- In reply to: Jeff Boyce: "Re: DB design and normalization"
- Next in thread: Jeff Boyce: "Re: DB design and normalization"
- Reply: Jeff Boyce: "Re: DB design and normalization"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 15 Jun 2004 08:54:25 -0400
Well, let's see if I can give you something like what you are looking for...
First I would have a table containing my trial balance:
tblTrialBalance
AccountID
AccountDesc
Balance
GroupID
The account ID above would be a primary key and would be the account number
(since you cannot have duplicate account numbers in a trial balance, it
doesn't make sense to create another field for the PK). The GroupID field
would be a foreign key which would correspond to tblGroups which I define
below. Now that I'm sitting here thinking about it, I think I already
answered part of my question. But moving on, the tblGroups table would
represent pages and lines in a report. So maybe I'd have a table that looks
like this:
tblGroups
GroupID
GroupPage
GroupLine
GroupLineDesc
GroupSubLine
GroupSubLineDesc
GroupColumn
If you look at my original post, maybe you can now see where I'm trying to
go with this. My question regarding normalization concerns the fact that
(obviously) each page would have more than one line so to reduce redundancy,
would I create another table with the page names then use a PageID foreign
key? Again, thinking about this while typing, it makes sense from a memory
standpoint so maybe that is the way to go. I'm not sure where to go from
there. Obviously, each page of the report would have a line 1 and more than
one page could have a subline 1.1, but making separate tables to sort all of
this out doesn't seem to make sense. Also, depending on the page of the
report I'm trying to end up with, each page has varying number of columns,
so page 1 could have 5 columns while page 2 might have only 2. So would I
have a GroupLine/GroupSubline in tblGroups to accomodate each column? I
know normalization requires elimination of redundancies to some extent, but
I'm not sure how far I have to go to create a normalized database that would
be easy to maintain.
In the end, I would hopefully be able to run an update query to update the
GroupID in tblTrialBalance then run a query to generate the report I need
based on joining tblTrialBalance and tblGroups.
I hope this helps you understand the question.
TIA
Mike
-- Michael J. Malinsky Pittsburgh, PA "I am a bear of very little brain, and long words bother me." -- AA Milne, Winnie the Pooh "Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:uIw13KnUEHA.2524@TK2MSFTNGP12.phx.gbl... > Michael > > I also suffer from limited perception -- I'm having trouble visualizing the > underlying data from which you wish to create a report. > > I will point out, however, that a report is really not the same as the data. > If you have designed your data tables to reflect your report specifications, > I predict serious difficulties making it work! > > Can you provide a brief summary of your table structure -- something along > the lines of: > > tblAccount > AccountID > MainAccount# > SubAccountID > > tblSubAccount > SubAccountID > SubAccountTitle > > ... > > -- > More info, please ... > > Jeff Boyce > <Access MVP> >
- Next message: Jay Vinton: "RE: Custom AutoNumber"
- Previous message: Plover Rover: "ActiveX Error"
- In reply to: Jeff Boyce: "Re: DB design and normalization"
- Next in thread: Jeff Boyce: "Re: DB design and normalization"
- Reply: Jeff Boyce: "Re: DB design and normalization"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
