Designing a new DB

[Guest]

Hi,
I'm starting a brand new database. It is for the testing lab I work in.
Data will be entered daily. I originally planned to have a record for each
day of the year. IE: Record 1 will be Jan 1 and the last record in the DB
will Record 365 (Dec. 31).

But.... the more I think about it, should I maybe have 12 tables for each
month instead? Or go with one big table that contains all 365 records and
use queries for each month? I will need a report at the end of each month
and a final annual report at the end of the year. There will be simple
calculations in certain fields in both the monthly and annual reports.

I hope I explained this properly. I've built other DB's, so I understand
tables, queries, reports etc. but I'm still learning.

Thank you for your time.
Amy

 

[Sandra Daigle]

Hi Amy,

Stay with one table and use queries and/or reports to group your data by
month.

*IF* you were to go with 12 tables your table names would begin to represent
data (name of month) and you would have to write some ugly union queries to
summarize more than one month. This would be a horribly un-normalized and
difficult to maintain design.

You might want to do some reading on Normalization - it might not click at
first but work through some examples and read the rationale behind each part
of it and it will start to make sense. Here are some links that might be
helpful:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

ACC2000: "Understanding Relational Database Design" Document Available in
Download Center
http://support.microsoft.com/?id=234208

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Database Design Tips - Michael J. Hernandez
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

Also check this page for links to several articles and a presentation on
Normalization basics:

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

There are also several books which have good discussions about logical
design and database normalization - two that I like are:

a.. Hernandez, Michael. Database Design for Mere Mortals 2nd Edition. :
Addison-Wesley Developers Press, March 2003

b.. Riordan, Rebecca. Designing Relational Database Systems

 

[Guest]

Ms. Daigle,
Thank you answering my question. I will go with one table and filter my
monthly info with queries, and then design reports behind those.
Thank you also for taking time to include the mentioned Linkage Help sites.
I've begun to research them.
Regards,
Amy

Hi Amy,

Stay with one table and use queries and/or reports to group your data by
month.

*IF* you were to go with 12 tables your table names would begin to represent
data (name of month) and you would have to write some ugly union queries to
summarize more than one month. This would be a horribly un-normalized and
difficult to maintain design.

You might want to do some reading on Normalization - it might not click at
first but work through some examples and read the rationale behind each part
of it and it will start to make sense. Here are some links that might be
helpful:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

ACC2000: "Understanding Relational Database Design" Document Available in
Download Center
http://support.microsoft.com/?id=234208

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Database Design Tips - Michael J. Hernandez
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

Also check this page for links to several articles and a presentation on
Normalization basics:

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

There are also several books which have good discussions about logical
design and database normalization - two that I like are:

a.. Hernandez, Michael. Database Design for Mere Mortals 2nd Edition. :
Addison-Wesley Developers Press, March 2003

b.. Riordan, Rebecca. Designing Relational Database Systems


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,
I'm starting a brand new database. It is for the testing lab I work
in. Data will be entered daily. I originally planned to have a
record for each day of the year. IE: Record 1 will be Jan 1 and the
last record in the DB will Record 365 (Dec. 31).

But.... the more I think about it, should I maybe have 12 tables for
each month instead? Or go with one big table that contains all 365
records and use queries for each month? I will need a report at the
end of each month and a final annual report at the end of the year.
There will be simple calculations in certain fields in both the
monthly and annual reports.

I hope I explained this properly. I've built other DB's, so I
understand tables, queries, reports etc. but I'm still learning.

Thank you for your time.
Amy