Newbie help !!! - DB Design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steve - 10 Jun 2004 20:03 GMT

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

Reply to this Message

PC Datasheet - 10 Jun 2004 20:59 GMT

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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> Hello,
> I was wondering if any of you could give me some hints and or guide me to
[quoted text clipped - 79 lines]
>
> thanx in advance

Reply to this Message

Albert D. Kallal - 11 Jun 2004 01:46 GMT

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

>> So I thought VB + Access. I can't go to SQL server, i have no access to

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.

>>(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 ?)

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

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn

Reply to this Message

Jamie Collins - 11 Jun 2004 15:02 GMT

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

--

Reply to this Message

rpw - 11 Jun 2004 01:54 GMT

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!)

Signature

rpw

> Hello,
> I was wondering if any of you could give me some hints and or guide me to
[quoted text clipped - 79 lines]
>
> thanx in advance

Reply to this Message

Fred Boer - 11 Jun 2004 02:46 GMT

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
[quoted text clipped - 16 lines]
>
> 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!)

> > Hello,
> > I was wondering if any of you could give me some hints and or guide me to
[quoted text clipped - 79 lines]
> >
> > thanx in advance

Reply to this Message

Jeff Conrad - 11 Jun 2004 06:33 GMT

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

--
Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message

Lynn Trapp - 11 Jun 2004 16:40 GMT

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

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

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Reply to this Message

Jeff Conrad - 11 Jun 2004 19:48 GMT

> > ....you will all become one with the Access Borg....
>
> RESISTANCE IS FUTILE!!!!!!!!!!!!!

ROFL!!

:-)

--
Jeff Conrad
Access Junkie
Bend, Oregon

Reply to this Message

LMB - 13 Jun 2004 14:50 GMT

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

Linda

> >Dear rpw, Steve:
> >
[quoted text clipped - 17 lines]
> Access Junkie
> Bend, Oregon

Reply to this Message

steve - 11 Jun 2004 15:59 GMT

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