Обсуждение: What are the consequences of a bad database design (never seen that before !)

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 ...

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 ....

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

Thanx for any help

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

> 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

  I have been working with databases for web scripts (PHP) and all sorts
of stupid billing systems and databases. Right now my system uses PHP to
access mysql, postgres and mssql databases in the same time. The only
possible solution for many of these problems was to create by own
database with my own tables and create a script to replicate the data
from one to the other periodically.
   I think that you should try to move the system to your own way of
thinking and doing things and leave them the alternatives. Sooner or
later they will use your system as it is better.


-------------------------
E-Mail powered by MadNet.
http://www.madnet.ro/

On Mon, Apr 11, 2005 at 09:39:13AM +0000, Jinane Haddad wrote:
> 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 ....

Personally, I've never actually gone so far as to effect this on a
large scale. But sometimes when something is screwed up I create the
new structure and then create a VIEW so other parts don't notice. Then
the only bits you need to change are the bits that change the table.

The main problem I find is these applications don't check for errorss
or use transactions properly. Hence adding records sometimes fails and
the program never notices. Ooops.

As for long term effects, the value of your data is reduced to
maintainence and due to possible errors...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Le lundi 11 avril 2005 à 09:39 +0000, Jinane Haddad a écrit :

> 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.

> 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.

Why did they hire you? If it is because of your PostgreSQL knowledge
then I suggest you:

    - do a quick private audit of the problems and document them well
    - arrange a meeting with your superior and tell him what you have
found
    - propose solutions and give a rough estimate of time frame to fix
what is broken

Cheers

Tony

On Mon, Apr 11, 2005 at 01:00:43PM +0300, Costin Manda wrote:
> > 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

The consequences are what they're seeing already: bad data and slow
queries.  Not fixing this will only make things worse.

> I have been working with databases for web scripts (PHP) and all
> sorts of stupid billing systems and databases.  Right now my system
> uses PHP to access mysql, postgres and mssql databases in the same
> time.  The only possible solution for many of these problems was to
> create by own database with my own tables and create a script to
> replicate the data from one to the other periodically.

I think you've just found the classical case for using DBI-Link :)
http://pgfoundry.org/projects/dbi-link/

> I think that you should try to move the system to your own way of
> thinking and doing things and leave them the alternatives.  Sooner
> or later they will use your system as it is better.

That, or you'll get sacked. :/

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

On Mon, 2005-04-11 at 04: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 ...
>
> 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 ....
>
> 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

First off, make your escape plan, it's likely that these people are too
clueless to be saved.  Seriously, I've seen it happen before.  You can
have all the evidence at hand as to why what they're doing is wrong, and
make the strongest business plan there is, and some guy who's been in
charge of this beast for two years and cares more about his ego will
throw his political weight around to crush you, the new guy who "knows
nothing about our business."

Next, you need to get the ears of the people who hired you and explain
to them WHY the current design is so bad.  Use concrete examples, and
teach them some very basic relational theory and how it fixes these
problems.  You have to make allies here, not enemies.

Finally, if you can't stop them, they may write the next peoplesoft!
Haha.  Just Kidding, Only Serious.

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

>>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.
>
>
>
I second this advice.  Remember that (to business people) bad database
design is not a critical problem unless it interferes with critical
business goals.  What I would do is to lay low and do what they tell you
for several months, making your own observations about how the database
actually works and keeping track of potential data design fixes (i.e.,
combine these tables, replace this column with a foreign key to that
one, etc).

If the database design is as bad as you say, in not too long you will
come to a situation that would be best handled with normalized data.
Then bring it up, saying, "We could re-structure the data to look like
this so that X query could be computed with a single select with minimal
rewriting of module A and a few queries in module B."

At that point, your employers will hopefully say, "Oh, yes, that's a
very clever solution."  Then you implement your fix and take the credit
for this.  Ideally, over time, you will build up a reputation as a
problem solver and gain more responsibility for database design and thus
have more ability to fix the underlying problems.

Regards,

Paul Tillotson

Thanx guys for the advices.

i think i will have to find some "POLITICAL" approach in order to
restructure the existing database, which i am not so good at but worse
trying. Note that even the code is Bad (they are using PHP for a big
application - no object oriented design - a lot of code redundancy ...).

However, it seems difficult to fix the database bit by bit cause as far as i
have seen one or more  primary TAble(s) are missing !! So instead of using
an ID, 3-4 fields are being rewritten in almost every table ! So if i have
to build the primary tables, i have to change all the other tables replacing
the combined fields with the corresponding ID ... and there is many others
modifications which could lead to eventuel code modification even if i
change the Views in order to mask the changes. (Thanx god they are using
Views !!!!!)

Anyways it seems i have a major modification that will need time and they
are giving me Time for adding modules not the time for fixing the existing.

So basically what is happening is du to the bad database and code design:
Writing a simple Task is becoming difficult and requires minimum 4 times
more time than in the case of a good design.
So development time is wasted, and data Corrections are being done almost
every day by the stuff here ...

Thanx again.
Jinane.

_________________________________________________________________
Don't just search. Find. Check out the new MSN Search!
http://search.msn.com/

On Wednesday 13 April 2005 01:21, Jinane Haddad wrote:
> Thanx guys for the advices.
>
> i think i will have to find some "POLITICAL" approach in order to
> restructure the existing database, which i am not so good at but worse
> trying. Note that even the code is Bad (they are using PHP for a big
> application - no object oriented design - a lot of code redundancy ...).
>
> However, it seems difficult to fix the database bit by bit cause as far as
> i have seen one or more  primary TAble(s) are missing !! So instead of
> using an ID, 3-4 fields are being rewritten in almost every table ! So if i
> have to build the primary tables, i have to change all the other tables
> replacing the combined fields with the corresponding ID ... and there is
> many others modifications which could lead to eventuel code modification
> even if i change the Views in order to mask the changes. (Thanx god they
> are using Views !!!!!)
>
> Anyways it seems i have a major modification that will need time and they
> are giving me Time for adding modules not the time for fixing the existing.
>
> So basically what is happening is du to the bad database and code design:
> Writing a simple Task is becoming difficult and requires minimum 4 times
> more time than in the case of a good design.
> So development time is wasted, and data Corrections are being done almost
> every day by the stuff here ...
>

Remember that the goal is to fix everything *now*... but fix it bit by bit.
The first time you would need to access those 3-4 fields in any new module,
rather than adding them into a new tables, rework the schema to be
normalized...even if you cant pull those 3-4 fields out of every table, pull
it out of a core few tables and use your new key in your new tables so that
you start down the path to a better schema.

But be careful how you approach things... have a 3-4 field primary key in 10
different tables is perfectly fine within the relational model... in fact
some purists would even argue for something like that rather than creating a
surrogate key... so just because they have done that doesn't mean that they
are wrong even if your way is better.

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