Help with database structure!

[Tony Williams]

I'm looking for help on database construction. We have a current database
that would appear to be not normalised so I am looking for help on the
correct structure. Here is our problem.

We have 40 companies that supply us year to date data on a quarterly basis.
The data covers different areas of business activity. There are 7 main
groups that I will call Types 1 to 7 and within that we get data on two
subcategories that I will refer to as Type1A and Type1B and so on within
those 7 groups. The sum of Type1A PLUS Type1B EQUALS the value of Type1 so
the data comes in like this:



Type1A + Type1B=Type1

Type2A + Type2B=Type2

Type3A + Type3B=Type3

Type4A + Type4B=Type4

Type5A + Type5B=Type5

Type6A + Type6B=Type6

Type7A + Type7B=Type7



These equations represent the format in which the numerical data is sent to
us. All the above are numbers with 3 decimal places.



Each company sends us a form with this data.



At the end of each quarter we need to produce a report that summarises the
40 companies' figures. The report for say December 2004 would look like this



Qtr ended
YTD

Dec 04 Dec03 Dec
04 Dec03

Total of Type 1 2222.222 1111.111 44444.444
55555.555

Total of Type 2 2222.222 1111.111 44444.444
55555.555

Total of Type 3 2222.222 1111.111 44444.444
55555.555

Total of Type 4 2222.222 1111.111 44444.444
55555.555

Total of Type 5 2222.222 1111.111 44444.444
55555.555

Total of Type 6 2222.222 1111.111 44444.444
55555.555

Total of Type 7 2222.222 1111.111 44444.444
55555.555



You will see that we need to calculate the "quarter" totals which is in the
above example the difference between the totals for December 04 and the
totals for September 04 and similarly for 03.



Is anyone able to point me in the right direction as to how we should
consider constructing the database given that we need to store the values
for each type and subtype, the company providing the data and the quarter
date to which the data applies.

Thank you

Tony

 

[John Nurick]

Hi Tony,

Perhaps something like this. * indicates that a field is, or is in, the
primary key.

tblActivityCategories
ActivityCategory*
maybe other fields related to the ActivityCategory

tblActivitySubCategories
ActivitySubCategory*
ParentCategory* FK into tblActivityCategories

tblCompanies
CompanyID*
CompanyName
maybe other fields related to the company, e.g.
SIC, Country, State, PostCode

tblData
CompanyID* FK into tblCompanies
DataYear*
DataQuarter*
ActivityCategory* FK into tblActivityCategories
ActivitySubCategory* FK into tblActivitySubCategories
DataValue

If you are going to be storing millions of DataValues, you could reduce
the mdb size (at the cost of needing more complex queries) by adding a
unique Long value to each ActivitySubCategory record and using that as
the foreign key in tblData instead of the two text fields that are there
now.

 

[Guest]

Thanks John. Could you explain what FK is? I assume it is Foreign Key which
I've seen mentioned elsewhere but what actually is it?
Thanks again
Tony

 

[Guest]

John, in tbldata two fields are marked as Primary Keys Access seems to let me
have only one? I am using Access 2000. Also are these two fields DataYear and
DataQtr date fields?
Thanks

 

[Guest]

John I've tried your suggested layout and I'm not sure it fits what I'm
looking for. We input the figures from a form that the companies send to us.
Is there any way I can send you a copy of the form so that you can see what
the structure is?

 

[John Nurick]

A foreign key is (usually) one field in one table that contains the
primary key values of related records in another table.

Open the Northwind sample database that's installed with every copy of
Access and check out the Orders and Order Details tables: [OrderID] is
the primary key of one and a foreign key in the other.

 

[John Nurick]

Tony,

A table can only have one primary key, but a PK can include more than
one field. Instead of right-clicking on the field in Table Design View,
select both the fields and then use the Primary Key command on the Edit
menu.

If you're only getting quarterly data it's simpler not to use Date/Time
fields. The DataYear field should be a Number (Integer). If you number
your quarters, DataQtr should be Number (Integer) too. If the data
refers to specific dates, or even just to months, I'd use a single
Date/Time field. (By the way, I called it DataYear because Year is the
name of a VBA function, and having a field with the same name as a
function can cause confusion.

 

[John Nurick]

Tony,

Don't confuse the layout of the form and the structure of your data. The
great thing about relational databases such as Access is that they
separate the way the data is *stored* from the way it is *presented*.

First, you need to understand your data and create a relational
structure that will accommodate it. When the structure is right, you
start designing the data entry forms and the reports. If you try to make
the *structure* reflect the layout of existing paper forms you're likely
to end up with a database that can't create the reports you need - or
can only do so with contorted code and queries.

Once the structure is right it's always possible to build data entry
forms that mimic just about any paper forms (although it's sometimes a
lot of work) - but often it's better to compromise with something that's
easy to use and not to hard to build.

 

[Guest]

Thansk John some useful tips there. I'll go away and try rethinking what
we're doing
Tony