Re: [ic] Database design questions

[Dan B <db@cyclonehq.dnsalias.net>]

At 05:30 PM 4/6/2001 -0500, you wrote:
>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.

Your comments on the database design are most welcome.  Please continue to 
analyze and critique it.  In my opinion, duplication all the customer 
information, order_cost, etc. is reasonable.  It's a little faster (one row 
for all the data), and it allows historic orders to accurately retain the 
information that was used at the time, rather than the current customer 
data, if it is changed.  You might dig up the short thread from last year 
about Mike Heins' normalization preferences.  :-)

As far as foreign keys, you can easily add foreign keys to your given 
database (dbconf/... files).  But I imagine it's not in the distributed 
template because there is no cross-database compatibility-layer for foreign 
keys.  I.e., oracle does foreign keys with command, pgsql with yet another, 
etc., ad nauseam.

However, Mike recently made another amazing feat when he skillfully added 
the beginnings of cross-platform support for transactions in 4.7.x.  Last I 
heard, it has only been tested on pgsql, but I imagine someone will do it 
on Oracle soon.

Therefore, it would be considerable that someone Foreign keys could be 
built in.  One problem with foreign keys is that MySQL does not support 
them (unless the new berkeleydb backend does, anyone know?).  But the value 
of adding foreign keys is clearly seen by all the questions like: "Why when 
I delete from products is it still in the other tables, like pricing and 
inventory)?"

Foreign keys support would nip that in the bud, as well as increase 
all-around integrity of the data in the database.  I don't think RH 
(Akopia) lives in a glass house either, so it would probably be fine to 
submit patches for clearly better schemas, if one were to come out of this 
thread.

>Maybe that's overkill, but before I write applications to export the data to
>other apps/format (i.e. Quickbooks), I'd like to think the initial structure
>will be suitable for our long-term needs.

Our database has grown 10 times the size of construct (additional 
fields/tables) and flexibility has so far not been a problem.  But only you 
know your needs best.  Have fun,

Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com


_______________________________________________
Interchange-users mailing list
Interchange-users@lists.akopia.com
http://lists.akopia.com/mailman/listinfo/interchange-users