database
Jim Herrmann
kclug at itdepends.com
Mon Apr 25 20:11:32 CDT 2005
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/
More information about the Kclug
mailing list