[ic] Database design questions

[Jud Harris]

Mike, Dan,

Thanks for the replies.  Mike, thanks for sharing your thoughts on the design.
I expected that someone wiser than I had already hashed through these questions.
I speak as a rookie with enough knowledge and terminology to be a bit dangerous
(I'm in a database design class at school right now, finishing off my CS minor).

I definitely see how problems could occur if transactions linked to another
tables address data.  It would indeed cause old, modified data to be lost.

Before I spend time writing a perl dbi script to generate something Quickbooks
can import, does anyone know if its been done already, or if there's a quicker,
better way to do it via the reporting util in IC?

Thanks again,
Jud

----- Original Message -----
From: "Mike Heins" <mikeh@minivend.com>
To: <interchange-users@lists.akopia.com>
Sent: Friday, April 06, 2001 6:00 PM
Subject: Re: [ic] Database design questions


> 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
>
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users
>