[ic] Normalized database for the Foundation demo.

Mike Heins interchange-users@icdevgroup.org
Mon Jul 22 12:02:01 2002


Quoting * * (dominique_kongolo@hotmail.com):
> Hi Mike,
> 
> >In this case there is a very good reason.
> >
> >What if the customer changes their address after the order is shipped?
> >You will have no record as to where the order went -- maybe even where
> >it should go.
> >
> >With product descriptions and prices this is even more important.
> 
> I understand that, but using normalized tables wouldn't prevent us from 
> maintainng this kind of functional requirements. We would only have to use 
> other tables to track history.
> But my point is that the DB is NOT normalized and that's what I found 
> difficult to understand. My university 'Data Structure' teacher sounded so 
> convincing when talking about normalization :°).
> Interchange/foundation has been around for a while and users/developers 
> seems to be fine with it. So I'll dig deeper in it and see exactly what 
> potential problem I might have with it not being normalized and come back to 
> this mailing-list.

This is all fine when the database *is* the application. When it is
not, and is only a repository for it, then things are different.

Designing a rigid and inflexible system that requires all sorts
of effort to maintain data integrity is not what we are after.
Interchange's object is to generate sales orders, which are the
same things people still write on an order form. It is not an
accounting application, where 3NF is definitely appropriate.

A lot of people will tell you that you have to have type-checking
to have a reliable software application, ala Java or C++. It is
simply not true. Interchange has none of it, yet is pretty robust
as these things go. It is not perfect nor error-free, but then again
I have not seen a large application in a fast-moving environment like
e-commerce which is. 8-)


> 
> >To work with 3NF, we would have to have
> >processing filters for all sorts of things, and would probably have to
> >use foreign key constraints not available for MySQL (or design our own
> >importers that implemented those).
> 
> MySQL does support foreign key since InnoDB, and (explicit support of) 
> foreign keys are not a necessity to join tables.

This is all fine. But once you delve into the difficulties of making
all that portable between at least Pg, Oracle, and MySQL let me know
what you think. There is a reason that most applications that try and
achieve 3NF are not very portable between SQL implementations.

> 
> >It would also be possible to build some pseudo-constraints which
> >did some updating of transaction/orderline info when a customer
> >record changed.
> 
> We could, but this sounds like moving database layer constraints to the 
> business logic. I wonder what my 'Data Structure' teacher would say about 
> that :°) .
> 
> >That being said, it wouldn't be too difficult to produce a "foundation-3NF"
> >template which implemented a normal-form database structure if you wanted
> >to do that.
> 
> Yes, I would like to do that. Any idea of where I should start from ?
> 

No, because I can't help. I won't be using it except where it is
appropriate in interfacing to other applications. 8-)

-- 
Mike Heins
Perusion -- Expert Interchange Consulting    http://www.perusion.com/
phone +1.513.523.7621      <mike@perusion.com>

Few blame themselves until they have exhausted all other possibilities.
 -- anonymous