Newbie help !!! - DB Design

[steve]

Hello,
I was wondering if any of you could give me some hints and or guide me to
the right direction regarding the design of my database.
It's a relatively descent-sized project (for *my* programming standards...)
both on the size of the data and on the complexity of the queries.
Maybe because it's not a typical "employee db" or "accounting Dpt." where
you usually worry about the size since these are typical textbook examples
and the queries are standard.
(I think, anyways!)

Enough with the preamble, let me try and explain as much as I can.

Well there are 2 major "elements" :

(1) Meteorological data coming from approx. 20 stations, I'll elaborate ...
(2) Extracting data based on several parameters AND/OR doing elementary
statistics on them AND finally being able to save the data in different
formats (ASCII, Excel,...).

FOR (1)
The problem is that a **typical** station collects the following info:
date | time | .........24 columns (1/hr) of measurements for e.g. outside
temperature etc....... |
......
similar for other measurements e.g. wind direction, wind speed,
precipitation, etc. Around 10 different variables.

20 stations X 10 hourly measurements = 200 flat files

ON TOP OF THAT

similar measurements on other variables but on a *daily* basis, i.e. 1 value
for 24 hrs instead of 24. For example maximum daily temperature.
Therefore, let's say

20 stations X 5 daily measurements = 100 flat files

So a TOTAL of approx. 300 flat files containing the above info.

Some additional complexities involve the following:
- Not all stations come from the same body therefore their codes are
different (so we'll have to choose a different key ?)
(Obviously the flat files containing the data are different (different
sources) but i can transform that)

- Not all stations have all the variables (measurements) some have a few
others have only daily ones etc.

.... etc


FOR (2)

The user apart from the typical " give me the wind direction from station A
, B and C between 2000/03/06 till 2003/09/23 WHEN the AVERAGE temperature
exceeded 52 degrees" should also be able to get info like :
" print out the moving-average of wind direction from these stations on the
*summers* (june-july-august) only, for the period 1998-2003 "
etc. etc.

The extraction of data has to be as user friendly as possible within the
time constraints that i have.

Phewww!!!!

So I thought VB + Access. I can't go to SQL server, i have no access to it.
We need a relatively involved interface and I thought Access forms would not
be enough or at least it would be harder for me to hard-code a whole bunch
of conditions on radio buttons, grayed-out boxes, lists, etc. Do you agree
with my choice?

There are A LOT of details that complicate the project and I don't want to
list them here now, hopefully later on.

I need help with the design for now. I am still straggling with what tables
to make and what relationships to give them.

(BTW believe it or not dates are very important. I want to be able to stop
the user from choosing an invalid date e.g 31 June 2003, is this easy to do
, maybe in VB ?)

Any guidance and help would be GREATLY appreciated!

thanx in advance

 

[PC Datasheet]

There are outside resources that can provide you help from setting up the design
of the your tables to creating the database for you. These resources can greatly
shorten the time to get up and running compared to do-it-yourself. You also need
to determine if you have all the skills needed to do it yourself. I have seen
many cases where a large number of hours were put into a do-it-yourself project
only to discover that the tables were not designed correctly and all the work
had to be scrapped to go back to start over.

 

[Albert D. Kallal]

The first thing you want to start reading up on is what we call database
normalizing.

The above is just a fancy word for how you design, and model data systems.
It is likely the MOST important concept you can learn.

For example, often we have somehow who wants to store 12 months of sales
data. So, they instantly think we need 12 tables, one for each month.
However, why not just use one table, and add a month field?

So, for example, you have 20 stations, and each station reads data. So, do
you need a whole bunch of tables..or just like the 12 months of sales
data..can we normalize that data:


StationNum Date Time MeasureType
Amount


As you can see, with the above table, we can store temp, precipitation, wind
or whatever, and we only have ONE table.

Your data could look like:

StationNum Date Time MeasureType
Amount
7 06/01/2004 08:00 Rain
12
7 06/01/2004 08:00 Temp
54


Of course, for whatever type of measure you need, you just add a new Measure
type, and thus don't even need to add a new table each time a new type of
measurement is added (humidity, bar pressure etc etc).

So, all of a sudden from having a zillion tables for each station, and each
type of measurement, you now have one table.

The trick here is to NEVER use a field that is defined as one measure, like:

hour1 hour2 hour3 hour4.
5.4 5.7 5.9 6.0
The above is a bad, or what we call un-normalized design, since each field
name contains information about the data..and it should not. The above table
as normalized would be:

Hour Amount
1 5.4
2 5.7
3 5.9
4 6.0
it.

SQL server is just a data engine, and has no provisions for write the user
interface, or application side anyway. So, using sql server will not solve
any special problem for you. Sql server is JUST a data box that lets you
store data (you can JUST put tables on sql server..it does nothing in the
way of letting you build your application). So, sql server does not have any
ability to build forms etc (you can use VB, or ms-access to build the forms
for sql server if you want). So, often we use ms-access as the application
interface to sql-server.
the user from choosing an invalid date e.g 31 June 2003, is this easy to do
, maybe in VB ?)

You can, and should built a nice interface for users to select data. So,
instead of checking if the user entered the wrong date, just show them a
calendar..and they can NOT select the wrong date!

Here is some screen shots that will give you some ideas:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

 

[Guest]

Hi steve,

I tried posting earlier and that post hasn't shown up yet, so if when you read this one you've already read my previous post you can ignore this one.

If you are truly a newbie, and you are under time constraints to get this application developed, then maybe you should locate an experienced developer.

On the other hand, if you are ready to tackle this on your own, I have a couple of comments/suggestions. You explain that you think that there would be upwards of 300 flat-files. My interpretation of the data in the flat files suggests that you are familiar with spreadsheets (maybe even already have this info in spreadsheets) and are therefore suffering from "spreadsheet-itis". From your description, I can identify three subjects: Stations, Periods, and Measurements. Here is a sample table layout for those three subjects.

tblStation
StationID
LocationName
(other fields that describe 'station')

tblPeriod
PeriodID
PeriodDescription 'Hourly, Daily, Weekly, etc.....

tblMeasurements
MeasurementID
StationID 'the foreign key to link the station to the measurement
PeriodID 'identifies whether the measurements are Hourly or Daily
MeasDate 'the date of the measurement
MeasTime 'the time of the measurement
OutsideTemp
WindDirection 'this might be a foreign key to a wind direction table? N, NE, NW,?
(other fields for logging measurements....)

With these three tables you can log the measurements for all of the stations, for any time period. Then you can query the tables for averages for one or more stations for a given date range or other measurement period. If the tables are set up properly, then the queries are relatively easy.

I'm a newbie myself and I found that "Access 2003 Inside Out" was very useful to me in guiding me through the steps involved in developing an application in Access. Along the way I discovered this newsgroup and it has proven to be a tremendous help and source of support for me.

One of the things I learned about here was "Normalization". Jeff Conrad had posted a link to an article that details out what normalization is and how to use "normal forms" (not the same thing as forms in Access - more like stages of normalization). Maybe if he's reading this, he'll post it for you? Otherwise you might try a search for it on this site.

You should list all of the things that you want your application to do on one document and all of the subjects/tables on another document. Then with each subject, put it through the 'normalization' process before you actually build the table in Access.

Also, try not to confuse how you want to see the data with identifying the data subject. How the data is laid out on a report or in a form is not necessarily how the tables are configured.

Good luck on your project and come back here if you need help on a specific question/problem - there are many people here willing to help (and alot of them are experts too!)

 

[Fred Boer]

Dear rpw, Steve:

I've taken the liberty of copying one of Jeff's recent posts below. Is the
normalization link one of these, rpw?

HTH
Fred Boer

P.S. Don't be too intimidated by this list... you don't *have* to be an
Access Junkie like Jeff.. <g>


<start quote>

Hi,

Here's one or two links, grouped by category, to help in your Access qwest.
Please watch
out for any possible line wrapping on these links.

Number One on your start list:

http://www.mvps.org/access/
(A definite must!)

Microsoft Knowledge Base for searching:
http://support.microsoft.com/?scid=fh;[ln];kbhowto


Getting Started:

Naming Conventions:
http://www.xoc.net/standards/rvbanc.asp

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208
ACC2000: "Understanding Relational Database Design"

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

Interface Design topics:
http://www.isii.com/ui_design.html


Tutorial Style:

http://www.microsoft.com/Accessdev/articles/bapp97/toc.htm

Microsoft Access 2000: Building Applications with Forms and Reports
http://tinyurl.com/2szmm

http://www.dur.ac.uk/its/training/select/?selectCourse=IT47&campus=durham
(Sample practice file for download as well)

http://www.geekgirls.com/databasics_01.htm

http://mis.bus.sfu.ca/tutorials/MSAccess/tutorials.html

http://www.microsoft.com/office/previous/xp/columns/column06.asp

http://www.functionx.com/access/

http://www.bcschools.net/staff/AccessHelp.htm#Top

http://www.fontstuff.com/access/index.htm

http://www.fgcu.edu/support/office2000/access/

http://www.oit.duke.edu/ats/training/docs/access1/

http://cisnet.baruch.cuny.edu/holowczak/classes/2200/access/accessall.html#sec_intro


Sample Database Models/Table Designs:

http://www.databaseanswers.com/data_models/index.htm


Download Samples:

http://rogersaccesslibrary.com/TableOfContents3.asp

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

http://www.candace-tripp.com/access_downloads.htm

http://www.datastrat.com/DataStrat2.html


The mighty Sensei Leban's site for all things magical:
(Please bow in reverance on your way in.)

http://www.lebans.com/


Advanced Topics:

http://www.trigeminal.com/utility.asp

http://www.mentalis.org/index2.shtml


Misc: Downloads/Samples/Helpful Articles

http://www.granite.ab.ca/access/accesslinks.htm

http://allenbrowne.com/tips.html

http://www.viescas.com

http://www.daiglenet.com/msaccess.htm

http://www.applecore99.com/index.asp

http://www.helenfeddema.com/access.htm

http://www.dbases.net/knowledge_base/

http://www.aadconsulting.com/index.html

http://accdevel.tripod.com/

http://ffdba.com/downloads.htm

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

http://www.mvps.org/btmtz/


Helpful Articles/Code Samples:

http://www.pacificdb.com.au/MVP/MVPCode.htm

http://members.rogers.com/douglas.j.steele/AccessIndex.html

http://www.winsoft.sk/daofaq.htm

http://www.calvinsmithsoftware.com/HardToFindTips.htm

http://www.mvps.org/vbnet/

http://www.master-office.com/developers.htm

http://www.trevor.easynet.co.uk/AccFAQ/


A massive list of other Access links:

http://www.geocities.com/waddly/accory.html


Add-Ins/Utilities:

http://mphillipson.users.btopenworld.com/

http://www.fmsinc.com/

http://www.moshannon.com/

http://www.rickworld.com

http://www.dbi-tech.com/default.asp

http://www.zada.com.au/accessaddins.htm

http://www.mztools.com


Access User-Level Security:

Security FAQ (the Security Bible):
http://support.microsoft.com/?kbid=207793

Jack Macdonald's Security Document:
http://www.geocities.com/jacksonmacd/AccessSecurity.html

Lynn Trapp's Ten Security Steps:
http://www.ltcomputerdesigns.com/Security.htm


Creating menu bars and tool bars:

http://www.microsoft.com/Accessdev/articles/bapp97/chapters/ba01_6.htm


Book Recommendations:

http://www.developershandbook.com/
(A VERY good book to have)

http://www.viescas.com/Info/books.htm

http://www.fmsinc.com/toplevel/books.htm


Some of the MVP web sites:

http://www.mvps.org/links.html


Database Recovery:

http://www.pksolutions.com/


Search Google archives on Access newsgroup posts:

http://tinyurl.com/ctj2


That should keep you busy for a while! ;-)

Good luck,
Jeff Conrad
Access Junkie
Bend, Oregon
<end quote>

Hi steve,

I tried posting earlier and that post hasn't shown up yet, so if when you

read this one you've already read my previous post you can ignore this one.

If you are truly a newbie, and you are under time constraints to get this

application developed, then maybe you should locate an experienced
developer.

On the other hand, if you are ready to tackle this on your own, I have a

couple of comments/suggestions. You explain that you think that there would
be upwards of 300 flat-files. My interpretation of the data in the flat
files suggests that you are familiar with spreadsheets (maybe even already
have this info in spreadsheets) and are therefore suffering from
"spreadsheet-itis". From your description, I can identify three subjects:
Stations, Periods, and Measurements. Here is a sample table layout for
those three subjects.

tblStation
StationID
LocationName
(other fields that describe 'station')

tblPeriod
PeriodID
PeriodDescription 'Hourly, Daily, Weekly, etc.....

tblMeasurements
MeasurementID
StationID 'the foreign key to link the station to the measurement
PeriodID 'identifies whether the measurements are Hourly or Daily
MeasDate 'the date of the measurement
MeasTime 'the time of the measurement
OutsideTemp
WindDirection 'this might be a foreign key to a wind direction table? N, NE, NW,?
(other fields for logging measurements....)

With these three tables you can log the measurements for all of the

stations, for any time period. Then you can query the tables for averages
for one or more stations for a given date range or other measurement period.
If the tables are set up properly, then the queries are relatively easy.

I'm a newbie myself and I found that "Access 2003 Inside Out" was very

useful to me in guiding me through the steps involved in developing an
application in Access. Along the way I discovered this newsgroup and it has
proven to be a tremendous help and source of support for me.

One of the things I learned about here was "Normalization". Jeff Conrad

had posted a link to an article that details out what normalization is and
how to use "normal forms" (not the same thing as forms in Access - more like
stages of normalization). Maybe if he's reading this, he'll post it for
you? Otherwise you might try a search for it on this site.

You should list all of the things that you want your application to do on

one document and all of the subjects/tables on another document. Then with
each subject, put it through the 'normalization' process before you actually
build the table in Access.

Also, try not to confuse how you want to see the data with identifying the

data subject. How the data is laid out on a report or in a form is not
necessarily how the tables are configured.

Good luck on your project and come back here if you need help on a

specific question/problem - there are many people here willing to help (and
alot of them are experts too!)

 

[Jeff Conrad]

Dear rpw, Steve:

I've taken the liberty of copying one of Jeff's recent
posts below. Is the normalization link one of these, rpw?

Thanks for the assist Fred, I've been a bit busy.

P.S. Don't be too intimidated by this list... you don't
*have* to be an Access Junkie like Jeff.. <g>

And why not?


.....you will all become one with the Access Borg....

<vbg>

 

[Jamie Collins]

...

For example, often we have somehow who wants to store 12 months of sales
data. So, they instantly think we need 12 tables, one for each month.
However, why not just use one table, and add a month field?

So that's one table for each year, then?

Jamie.

--

 

[steve]

Thanx for the valuable suggestions.
However i believe that putting all the measurements in one table is not a
good idea. There is going to be a lot of empty space since for a specific
station and date if you have hourly measurements then you need 24 X (# of
variables you measure, e.g temperature) so you will need that many extra
fields in the table. However for daily measurements you will need 1/24 of
these fields (1 per day not 24 per day). Therefore the rest of the fields
will be empty.
UNLESS you mean to put the measurements in the same field which is
identified only by wjat you measure (e.g. Temp_meas ) regardless of how many
individual values it holds. Can we do this? If yes how would i separate the
values for each hour (commas ?) and how would i access the value of an
individual hour (e.g. 3 pm) ?
This is a very important point.

Thanx

Hi steve,

I tried posting earlier and that post hasn't shown up yet, so if when you

read this one you've already read my previous post you can ignore this one.

If you are truly a newbie, and you are under time constraints to get this

application developed, then maybe you should locate an experienced
developer.

On the other hand, if you are ready to tackle this on your own, I have a

couple of comments/suggestions. You explain that you think that there would
be upwards of 300 flat-files. My interpretation of the data in the flat
files suggests that you are familiar with spreadsheets (maybe even already
have this info in spreadsheets) and are therefore suffering from
"spreadsheet-itis". From your description, I can identify three subjects:
Stations, Periods, and Measurements. Here is a sample table layout for
those three subjects.

tblStation
StationID
LocationName
(other fields that describe 'station')

tblPeriod
PeriodID
PeriodDescription 'Hourly, Daily, Weekly, etc.....

tblMeasurements
MeasurementID
StationID 'the foreign key to link the station to the measurement
PeriodID 'identifies whether the measurements are Hourly or Daily
MeasDate 'the date of the measurement
MeasTime 'the time of the measurement
OutsideTemp
WindDirection 'this might be a foreign key to a wind direction table? N, NE, NW,?
(other fields for logging measurements....)

With these three tables you can log the measurements for all of the

stations, for any time period. Then you can query the tables for averages
for one or more stations for a given date range or other measurement period.
If the tables are set up properly, then the queries are relatively easy.

I'm a newbie myself and I found that "Access 2003 Inside Out" was very

useful to me in guiding me through the steps involved in developing an
application in Access. Along the way I discovered this newsgroup and it has
proven to be a tremendous help and source of support for me.

One of the things I learned about here was "Normalization". Jeff Conrad

had posted a link to an article that details out what normalization is and
how to use "normal forms" (not the same thing as forms in Access - more like
stages of normalization). Maybe if he's reading this, he'll post it for
you? Otherwise you might try a search for it on this site.

You should list all of the things that you want your application to do on

one document and all of the subjects/tables on another document. Then with
each subject, put it through the 'normalization' process before you actually
build the table in Access.

Also, try not to confuse how you want to see the data with identifying the

data subject. How the data is laid out on a report or in a form is not
necessarily how the tables are configured.

Good luck on your project and come back here if you need help on a

specific question/problem - there are many people here willing to help (and
alot of them are experts too!)

 

[Lynn Trapp]

....you will all become one with the Access Borg....

RESISTANCE IS FUTILE!!!!!!!!!!!!!

 

[Guest]

Hi Steve,

Also, THANK YOU to Fred and Jeff for posting the links list. This is the link on "Understanding Normalization" I had in mind. Steve, I think that it will prove to be very useful to you on this, and all future, projects.

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

My intent was to 'point' you to a different way of thinking about your data - by showing you that instead of 300 flat files of info you could have 3 tables that are related. But now you raise a point about having all of those measurements in one table and I'm not quite sure what how you are envisioning the tables and the data.

Let's assume that there are 30 measurements that each station must make. The table I proposed would have the StationID, the PeriodID, the MeasurmentID, a date field, a time field, and a field for each of the 30 standard measurements. If a particular station did not take all 30 measurements, then yes there is 'wasted' field space.

If you are thinking that you need 24 records for each station regardless of whether they have taken a measurement or not, then no that's not how this works.

Each record gets it's own unique ID by way of the MeasurementID field.
Each record would hold a link to the station info by way of the StationID field.
Each record would hold a link to the period info by way of the PeriodID field.
Each record would have a date.
Each record would have a time.
Each record would have 30 standard measurement fields.

I'm going to try to type out a few records in table format here to help you "see" it. For illustrative purposes only, instead of entering ID numbers for period and station, I'll put the name.

MeasID StationID PeriodID MeasDate MeasTime OutTemp WindDir WindSpd
1 Quebec Hourly 6/12/04 06:00:00 72 NNW 6
2 Florida Daily 6/12/04 07:00:00 97 SE 32
3 Quebec Hourly 6/12/04 07:00:00 73 NW 3
4 Quebec Hourly 6/12/04 08:00:00 74 N 1

Any location can have any number of entries - each entry is identified by the first five fields. You can set up the table so that it is indexed so there are no duplicates - that way no individual station could have two entries for the same period, date, and time.

Also, it is easy to see (based upon the data above), Quebec has an average temp of 73, winds out of the NW at 3.3 mph for the date of 6/12/04. Multiple flat files are not needed.

Post back if you have more questions.

 

[Jeff Conrad]

RESISTANCE IS FUTILE!!!!!!!!!!!!!

ROFL!!

 

[LMB]

I wouldn't like to be a Borg, I think those implants cause skin irritation.
I'll just study hard.

Linda

 

[Guest]

Steve,

Did this help you? Do you have more questions?