DB design and normalization

[Michael Malinsky]

I am attempting to create a database that will hold a trial balance then use
queries to "assign" the accounts to pages and lines on a report (a statutory
type of report). For each page, I will need a line number and description,
a subline number and description (if applicable) and a column number. My
question is which type of table would be more appropriate, something like:

Page LineNum LineDesc SublineNum Subline Desc Column
Pg1 1 Line1 1.1
Subline 1.1 1
Pg1 1 Line1 1.2
Subline 1.2 2
Pg1 2 Line2
1
Pg2 1 Line1
1
Pg2 1 Line1
2
etc.

or a separate table for each page, to eliminate the redundancy that exists
in the above example?

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]

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

....

 

[Michael Malinsky]

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]

Mike

Thanks for the clarifications. I'm not certain what information you are
storing in your tblGroup, but from your description, it sounds like you are
using the information somehow to control the layout of your report. Do you
also have the code you are using to take what's in the tblGroup and modify
the report in design mode, on the fly?

Or am I still not seeing the picture?

 

[Michael Malinsky]

Jeff,

I'm not as far as actually creating the "electronic" report yet, although I
do know what I want it to look like based on my paper copy.

The tblGroup will hold (unless suggested otherwise) the information as
described in my last post. These fields represent the pages, lines, etc. in
the paper form of the report I have. I guess since I know what the end
result needs to be, I'm trying to back into what my database schema should
look like. Right now, I don't even need to be concerned with the report
(actually, I'm not creating a report in Access, I'm using it as a back-end
for my Excel front-end which will be set up to look like the report I am
trying to end up with), I just need to end up with queries that will give me
my results (which I think I can do once the tables are set), but I want to
make sure my schema makes sense.

Based on the shema I outlined in my last post, it seemed a query selecting
all (or some) of the tblGroups.GroupIDs then seleting and summing
tblTrialBalance.Balance for the corresponding tblTrialBalance.GroupID would
do the trick.

Now that I've typed all of that, what I'm looking for is a direction on how
to take all of my paper report pages and properly convert them into a
database schema I can use to run queries against a trial balance which
results in a list of pages/lines that correspond to the pages/lines in the
paper report and sums the balances in the trial balance corresponding to the
appropriate page/line.

Does this make sense?

Thanks.

--
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]

Michael

I'll have to step back, as I am still not understanding what information
your second table is holding. I'm not familiar with using a table to hold
page, section, and/or line information.

My impression (but only a sense) is that you've defined a report first, then
tried to create a data structure to hold the information from that report.

If this is accurate, then I'll offer a suggestion. If you'll post an
example of the data itself, rather than the table/field names, perhaps one
of the other readers can get traction on what you're trying to do.