Re: What are the consequences of a bad database design (never seen that before !) - Mailing list pgsql-general

On Monday 11 April 2005 05:39, Jinane Haddad wrote:
> Hi everyone,
>
> i just got a new job in a small entreprise and they are using postgres as a
> database for their application. I was stupefied cause the database design
> is so bad : we can even say it has been done by amateurs. I observed the
> following problems till now:
>
> 1- redondancy ( TOO MUCH)
> 2- Many tables for the same object (stupid ex: a table for female_employees
> another for males ...) instead of one table (there are cases of 6 tables
> for the same one)
> 3- Some essential table are inexistant
> 4- Null values for critical information
> 5- Primary keys of multiple fields (4 or 5 sometimes) du to bad design
> ...
>
> The bottom of the line is that they have been working on the application
> for 2 years. Querys are becoming bigger and contains a lot of unions and
> "in/not in". The data contained in the database have to be checked often
> invalid values may be found ...
>

You need to figure out *why* they brought you in.  If they brought you in
because their current "database guru" was just to busy to do database work
full time, your going to need to approach things more carefully and make sure
to not denegrate any of the previous work.    If they brought you in because
they recognize that they are starting to have problems, then you can be more
straightforward about problems within the schema and better ways to approach
things.

> My question is with such database, what are the lomg term consequences or
> can we determinate them. I know that the querys will become slower, and the
> database will grow more quickly ... And a lot of information will not be
> trust wise ....
>

The two problems that will crop up are performance issues and bad data.

> But the people i am working with are not considering the restructuring of
> the database. They are even thinking of expanding it by adding new modules.
>
> Please can someone advise me, or tell me what to do, what may be the
> consequences
>

My advice is to not go to them with the "we need to totally reengineer the
schema for the next 6 months so that we have the same functionality we have
now" approach.   Instead figure out what the next module they want to add is
and what parts of the system it will touch upon and then see about
reengineering those particular parts of the schema.  The bit by bit approach
should get them to the same end game with stalling development for the next
few months.  Make sure to make use of views and stored procedures to help
keep backwards compatibility where you can't convince people to do code
modifications.  HTH.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL