How do I design a database based on the information that will be stored? - Copy of Tables and hirear

[Jim]

Hi I am trying to learn how to design a relational database. I have
designed one database in access 97 but it was a flat database file
since I didn't know how to do relational ones and needed it done
quick. I am using access 2002 to design this one but it will be read
from a visual basic program and displayed in an MSHFlexGrid.

Attached is an excell file (trimmed down and zipped) with the tables
that I need to store, I need to know how to decide how they should be
formatted in a database. Some of the tables have colums that are not
in other columns.

Do I make a database table for each of the tables? Do I make one big
table that has all the same colums in it and only make new tables for
the colums that are not in all of the tables? Is there some
other/better way? How do I know what/how to link the tables so that
they are efficient.

I figure there is probably alot to consider so I am not asking for the
absolute best/fastest/efficient way, just a basic start on how to do
it.

Also I am not looking to just have someone make the file for me so I
ask that if you tell me how to format it to please explaine how/why
you are saying to format it that way. The particular file for this
data is not realy important I am looking to learn how to make a
relational db.

Any and all help is appreciated. Thanks in advance!
Jim

 

[Jeff Boyce]

Jim

Shut off your computer and pick up paper and pencil. Forget how you have
set up "tables" in Excel -- the 'tool' constrains how you can organize the
data, and hence, your thinking.

What entities are you working with? For example, if you are working with
students, you might have the following entities:
Person
Course
Person-Taking-Course (i.e., a "student")

Jot those down. Now consider what pieces of information you will need about
those entities. For example, the first and last names of persons, the
subject matter of a course, the start-date of "Person-Taking-Course", and
maybe the identity of the teacher?! (this is also a fact about
"Person-Taking-Course").

When you have your entities and attributes, start drawing connections
between them -- i.e., the "relationships". In your world, does one student
take a single course, or can s/he take many?

Check Access HELP on "normalization".

Here's a list of other resources from Sandra Daigle:

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

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

ACC2002: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=kb;EN-US;283878

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

I generally also recommend the following two books since some people (myself
included) still enjoy and benefit from books.

a.. Hernandez, Michael. Database Design for Mere Mortals. Reading, MA:
Addison-Wesley Developers Press, 1997.

b.. Riordan, Rebecca. Designing Relational Database Systems