[ic] Database design questions

[Mike Heins]

Quoting Jud Harris (jud-lists@copernica.com):
> Hey folks -
> 
> Before I create 10 different catalogs for varying clients, I'd like to know
> whether or not the database structure of the contruct catalog is a good one.  I
> don't know too much about database design, but it does seem that there isn't
> very much 'relational' functionality within the current database design (mySQL,
> that is).  The transactions table seems to contain redundant data in many places
> (customer info, order_cost, etc, etc..), and doesn't use foreign keys and
> relations as it could.
> 

Can you give some specific examples of redundancy? You mention
transactions, so I will comment on that.  I know that one approach is to
create address book entries that, if the theoretical customer keeps their
address constant over time, could mean you just need to have a pointer to
that one-time entry in an address-book table. And that it sticks in the
craw of many people not to have a completely third-form database design.

This is inconvenient to manage for many businesses -- they would then
have a transactions database that required the address-book table entry
be present for them to process the order. And if that was corrupted or
managed improperly, or the pointer was mistakenly changed, boom! If an
order is placed against that record, it has to be marked as uneditable
except by administrators. And many, many other issues.

After dealing with abstracted database schemas over the years, I feel
that this approach is short-sighted for most businesses, especially
small businesses. It adds complexity with very little hope of gain.

The way it is set up now, an order transaction stands alone, even
to the point of not requiring the orderline database to summarize
overall sales revenue. Add the orderline table, and you have a complete
sales revenue history by item and affiliate. With the product description
and price *that was in effect at that point in time*.

The current setup was made in full knowledge of these issues. I considered
a highly-relational setup and decided to opt for simplicity.

And what will you gain? A few bytes on the disk in the age of multi-gig
hard drives? Even there, I think you don't win in most cases. You end
up keeping customer records around "just in case". I know you can add
constraints and cascades to do that type of garbage collection, but
unless you are on the multi-thousand-transaction-per-day level I think
all of this is overkill.

I feel you lose much more by not being able to delete or archive
customer records and transactions with simple queries. Perhaps it is
not elegant third-form design, but remember that third-form design
has its costs in complexity.

If you really think you will have millions of customers and transactions
per year, then you should do the type of large-scale database design
that you need. And Interchange will definitely support that.

But my guess is that is not necessary. If you have that level of
transactions, you need a full-blown ERP system with Interchange
only providing the front-end for taking orders. At that point,
this all becomes moot. 8-)

-- 
Red Hat, Inc., 131 Willow Lane, Floor 2, Oxford, OH  45056
phone +1.513.523.7621 fax 7501 <mheins@redhat.com>

Fast, reliable, cheap.  Pick two and we'll talk.  -- unknown