[thelist] Database Newbie - Tips?

Brooking, John John.Brooking at sappi.com
Wed Mar 9 13:31:44 CST 2005

Tod Richards said (in part):
> My problem is that I have a bit of information that I need to store in
> DB that I'm sure will requrie multiple tables.  I know how to "use"
> tables but I want to be sure that I am putting the correct information
> it should go, etc., in order to be efficient, secure, and reduce the
> for problems later.
> My question is (taking a deep breath) do any of you have any tips on
> to think about, or processes that you go through when setting up a new
> database, or any links on information gathering?  I spent most of
Sunday on
> google, but only really found a few useful pages.

I'm assuming that maybe you just didn't know the right search terms. I
think what you are asking about is  "Relational Database Theory". Google
"relational database introduction" and you'll find a lot of useful
things to read. Some of them may be a bit over your head (it can get
pretty abstract), but hopefully not all of them will be.

Here's my Very Short Summary for Beginners(TM): Each major "thing" in
your system should get its own table. There should be one or more fields
that together make each record unique, or you can create your own unique
key like an ID number. This is called the Primary Key. Each field on
those major tables is a property of that thing alone. You generally
should not store a value that is calculated from another value, because
then you will have to worry about keeping them in synch. If you have a
property that could repeat any number of times (like a person and a
history of their phone calls), that will require a separate table for
each instance of the property (each phone call), with a field to hold
the key value from the primary table, then the details of that instance.
This is called a Foreign Key Relationship, hence the "Relational" part
of the title. Most of your major tables will be "things" in your system,
or repeating instances related to those things. You'll probably also
have some miscellaneous tables for things like code values.

   Table PERSON, fields for name, address, phone, etc. plus a unique ID
# (Primary Key)
   Table PHONE_CALLS, fields PersonID (Foreign key to PERSON), date &
time, reason for call
This is a basic transactional (OLTP) database setup. For reporting
(OLAP), the rules may be different, as has been discussed here recently.
But I think this is the kind of information you are looking for?

Hope this helped.

- John 

This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.

More information about the thelist mailing list