On Mon, Feb 25, 2002 at 10:43:23AM -0500, Russell Hires wrote:
> Hey everyone,
>
> I've got a couple of projects going (several at work, one at home) that deal
> with using Databases. I'm finding that it's a big subject, and I know so
> little. Does anyone have any recommendations on what to look at (on the web,
> preferably) about dealing with design issues and such? I know that there are
> such places as mysql.com and postgresql.org that have documentation, and then
> there's doing google searches, but I'm hoping that personal experience and
> recommedations will be more helpful.
>
> I also know that there's at least a couple of people here that have database
> experience (ahem...Paul and Derrick :-)...
>
Well, it's not clear precisely what you want. If you really want to know
about "database design", then you're dealing with how to lay out the
data in the database "files" (tables). The thing to know about this is
"normalization", but that gives any normal person a headache. A long
time ago, I found a better, more practical treatment of this in a book
by Martin L. Rinehart, called "Client Server dBase Programming." Yeah, I
know, but it was very practical. (He also discusses normalization.)
Unfortunately, you probably would only find this book at the bottom of
your local trash dump these days. I'll paraphrase, and hope the author
never finds out.
The method is called "ORE&D", which stands for Objects, Events,
Relationships and Details. To tremendously shorthand it, first you list
the _objects_ related to your application. Like bumpers, alternators,
pistons, etc. Next, list the _events_ that impact your application. Like
engine assembly, frame assembly, interior installation. Next, the
_details_. This is trickier to explain, but an invoice is a good
example. An invoice consists of things like the Bill To, Ship To,
Invoice Number, Invoice Date, etc. These are what would be called the
"header" of the invoice. No matter how many line items are on an
invoice, there is only one "header". But those line items are the
"details". In the case of an invoice, you'd have a header table and a
detail table, which would have one record per line item. As part of each
record in the detail table, you'd have an invoice number, which would be
the thing that ties all the line items to that one record for that
invoice in the header table. Look searchingly through all the stuff
related to your application, and find out those things which might be
considered "details" as above. Lastly, you look at _relationships_.
Relationships come in four varieties: 1) one to one (one large pepperoni
pizza and one very fat consumer); 2) one to many (one header to many
detail lines; 3) many to one (many programmers, one project; a
many-to-one or one-to-many relationship mostly depends on how you intend
to look up the data most of the time); and 4) many to many (for
instance, you have a table for all your stores, a table for all your
products, and a many-to-many table that describes which products are
sold by which stores).
You go through the above and make tables for each object, event, and
each set of details. You tweak, and eventually, you've got it. I never
found database design particularly difficult, once I mastered a few
principles. You can read Codd all day, but I think you're better off
finding an application/program you understand, which uses databases, and
then examine the way those databases are built. I used to code an
accounting system called SBT, which had a rich set of about 20-30
tables. Looking at the tech specs for those tables taught me a lot about
the how and why of database design.
Hopefully, this is the type of thing you were looking for. If you need
more assistance, just ask. And of course, Derek has far more formal
education (and probably practical experience) in this than I do.
Paul
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 16:45:28 EDT