Re: [GENERAL] What are the consequences of a bad database design (never seen that before !)
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] What are the consequences of a bad database design (never seen that before !)
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] What are the consequences of a bad database design (never seen that before !)
On Mon, Apr 11, 2005 at 09:39:13AM +, 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 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. pgpySeyy049tj.pgp Description: PGP signature
[GENERAL] 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/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org