[ic] Database design questions

[Jean-Pierre Parent]

I've studied databases too at school and when I started working with
Interchange I found
out how to make complex databases very quickly as I wanted to go full
relational with pgsql.

Instead I ended up using MySQL and no foreign keys. Like Mike points out,
data integrity
comes with complexity. And as I did very nice things until now with
simplicity I have to admit
that the way the construct demo was done is not a bad idea. Not everyone
comes with
background in database design.

Besides...data integrity will require you to make more complex queries
sometimes at the price
of performance (like nasty triple joins, rarely something that kills servers
but still...).

As for the import/export thingy you can export in several formats (i.e.:
csv, tabs, pipes, commas,
etc). Usually programs that handle lots of data have tools to import/export
from these...I dont
know about Quickbooks. Perhaps you could start with Interchange's source
code? =)

Have fun!
Jean-Pierre Parent

----- Original Message -----
From: "Jud Harris" <jud-lists@copernica.com>
To: <interchange-users@lists.akopia.com>
Sent: Friday, April 06, 2001 7:12 PM
Subject: Re: [ic] Database design questions


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