database

[Jim Herrmann]

Tim,
It sounds like you are wanting to do a fairly simple database structure, 
which is a good way to get started.  When you say "open database 
format", I assume that you are talking about a target platform of a 
relational database, something like MySQL or Postgres.  Yes?  But that's 
getting ahead of the design curve.  Here's how to proceed if you want to 
follow something akin to a formal database design process.  I've been 
doing this for about 18 years as a DBA.  I hope I'm not volunteering too 
much information.  You said you were a newb.  :-)

The first thing you need to do is create a logical model.  When doing a 
logical model use English business names, and not programming 
abbreviations.  Identify all the *Entities* that you need to represent, 
and the *Relationships* between those entities.  A entity is a singular 
noun, which would include things like "Person", "Department", or in your 
case, "Reminder".  A relationship is a verb phrase like "has many", "has 
one", or "has zero or many".  So, you can say something like "a 
Department has many Persons" and "a Department has one and only one 
Manager".

Once you have this, you can create an Entity-Relationship (ER) diagram.  
This gives you a very high level view of your data structure.  This 
would be a box for each entity and a line between the boxes represents a 
relationship.  Exactly what the box and line looks like depends on the 
modeling tool you use.  Then you need to identify all the attributes 
(fields) for each entity.  You need to identify which attributes 
uniquely identify an occurrence (row) of that entity, and that's your 
natural key.  Keys of parents, of a relationship, migrate to the 
children.  If the parent is required for a child to exist, then it is an 
"identifying" relationship, and and the parent's keys become part of the 
child's keys.  If it is a "non-identifying" relationship, then some or 
all of the parent key is NOT part of the child's key.  Now, if you find 
an attribute occurs multiple times in an entity, then you should 
"normalize" those attributes into another entity or entities.  There are 
several levels of normalization, and I won't get into it here as it 
would be a full day class, but third normal form is considered the best 
level of normalization for most DB designs.  That's where each attribute 
of each entity is dependent upon the key, the whole key, and nothing but 
the key, so help me Codd.  (E.F. Codd, an IBM fellow, created relational 
database theory.)  :-)

Once you have a fully attributed ER diagram, you are ready to create a 
physical model based on the logical model you created.  This is where 
you create names that you want to use in your program, as you turn 
entities into *tables* and attributes become *columns* and relationships 
become *foreign keys*.  You might shorten "Department" to "DEPT", for 
example.  It's best if you use standard abbreviations, meaning be 
consistent, as much as possible.  It makes programming much easier.  The 
length of table and column names may need to be shortened, depending on 
the target database.  That's part of the physical design process.  You 
also need to decide what indexes will be on your tables.  Some for 
uniqueness, such as on the key, and some for performance, depending on 
how you are going to access your data.

There are also performance and convenience tricks that you can do when 
you take your model physical.  For example, if you have some natural 
keys that have lots of columns, you might introduce artificial keys, 
which would be something like an integer or reverse timestamp.  You may 
also at this point decide to "denormalise" some entities to make your 
system go faster, but that comes at a price of maintaining redundant 
data, and it breaks Codd's rule.  Depending on the DB platform, there 
are lots of physical implementation issues to consider, if you are 
building something with lots of data or lots of transactions, but you 
probably don't need to concern yourself with all that for this project.

Hopefully you find this information helpful.  If you have any further 
questions about data base, I'd be glad to help.  Sorry about the brain 
dump.  ;-)

Enjoy,
Jim

Tim reid wrote:

>I'm thinking of programing/writing/whatever a database to keep track
>of some information for work.  I think that I would like to keep track
>of 7-12 fields of information, and be able to set up reminders that
>are tagged to date information.  For instance, "you did such and such
>30 days ago...you can now file the claim, here is the information that
>you recorded earlier."
>
>How would i go about doing this, free as in beer, in an open database
>format?  Let me also mention that I'm a total newb  ;)
>
>TIA,
>
>Tim
>
>  
>

-- 
Progressive Values ARE American Values
Responsibility, Empathy, Freedom, Opportunity, Prosperity, Fairness,
Trust, Honesty, Open Communication, Community, Cooperation
http://musicalprogress.org/