[ic] database table redesign

[Mike Heins]

Quoting Jason (ic@santabarbara.org):
> Paul Jordan wrote:
> >>I was wondering if anyone has ever thought of redesigning the
> >>interchange table designs? They don't appear to be well normalized.
> >>I understand that IC can run on anything from text files to rdbms, so I
> >>was wondering if the tables aren't normalized because of a limitation
> >>due to the text or dbm tables or because it would just be too large a
> >>project. If anyone has any insight into how big a project this would be
> >>to really clean up the table designs for optimial usage by an rdbms like
> >>Oracle I would be interested in hearing from you.
> >>
> >>Thanks,
> >>
> >>Jason
> >>
> > 
> > 
> > I am sure some things could be improved. However I think you will have a
> > (big) headache upgrading WRT ADMIN if you make extensive changes. Said that,
> > remember tables like transactions and orderline offer historical and archive
> > values. Kind of like a "paper trail" for dispute resolutions and accurate
> > record keeping.
> > 
> > extreme 3nf I think is more for a specific application, and not just any
> > software that utilizes a rdb. Try 2.5NF
> > 
> > ;)
> 
> Agreed. So is it the general opinion of people on this list that the 
> current tables are really the best design? It is just so foreign to me 
> to see a database not normalized.
> 

I seem to have convinced most people. The ones I didn't convince
have left. 8-)

There have been about 5 identical queries over 7 years. At least
three have promised to give their 3NF version back, and no one
has.

3NF design is great for a rigid application where people will not be
customizing it at the database level. It can increase reliability. But
there have been no compelling arguments against our implementation; just
"it doesn't seem right".

3NF is not so great when people want to switch between databases
easily, or for rapid development. I have had battles with such
applications where I try to restructure something in the midst
of dozens of tables, only to fall victim to a constraint that
is buried in some other table definition or stored procedure.

Interchange encourages you to tailor the database to your needs;
particularly in the products area. Lack of constraints and relations
makes it easy to add and remove fields.

There is one part of IC which is relatively fixed in practice;
userdb, transactions, and orderline (because the UI needs to
have some consistency for its order applications).

We indeed have thought about adding an address database for
userdb, but no one has done the necessary work for it. Most of
the new features of IC have come from clients paying me (or 
occasionally others) to add them.

Bear in mind that IC will work just fine with a table structure that
you design, 3NF or no. It is just that you may have to design the UI
part, too. 8-)

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

I have a cop friend who thinks he ought be able to give a new ticket;
"too dumb for conditions".