Major DB Design Flaws

Strakos

I've recently moved to a new e-commerce project. One of my first tasks has been to review the current system design. In doing so I have uncovered significant flaws in the database design. In a database of with 107 tables, only 10 had any type of relationship defined between them. There are significant problems with data redundancy and integrity. I'm in the process of resolving these issues, however management is insistent that the site remains operational as well new development be pushed forward. My question is does any one have a suggestion on how to proceed? Keeping in mind that any changes to the DB design must have minimum impact on already deployed code.

bastien

it will have impact on the code as you will not need to query 107 table when you are done...

the best bet is to build it off line, test it and then migrate the data, where applicable to the new tables via phpmyadmin or some other tool...when all works fine, revamp some of the front end to show a new/improved site...

Strakos

Thanks,

That's along the lines I was thinking. Don't you just love it when you are handed a mess.

[deleted]

" In a database of with 107 tables, only 10 had any type of relationship defined between them."

That does not neccesarily mean the design is bad though, sometimes these things are done intentionally because adding a relation would adversely affect performance. What I'm really saying is that you shouldn't force yourself to do everything 'properly', because a 'properly' designed database sometimes just doesn't cut it.

"I'm in the process of resolving these issues, however management is insistent that the site remains operational as well new development be pushed forward. "

Ah management... gotta love those guys...

One thing is ofcourse clear as a bell, you can create an offline copy of the whole thing and re-program it to fit the new database design, but at some point in time you will have to shut the whole thing down for a couple of minutes to copy the live data to the new database design and switch the scripts over.

Personally I would not re-vamp the frontend at all, because it will draw more attention to it than before, and if the new site fails (which it will, Murphy says so) you'll lose face when you have to switch back to the old site.

That reminds me, ofcourse you're going to create backups of the old data, and you'll write a tool to migrate the data from the old database to the new, but it may be worth the effort to also create a tool to convert from the new database back to the old one, in case the new design collapses after a few days.

tomhath

That reminds me, ofcourse you're going to create backups of the old data, and you'll write a tool to migrate the data from the old database to the new, but it may be worth the effort to also create a tool to convert from the new database back to the old one, in case the new design collapses after a few days.

Truer words were never spoken. Whenever you redesign a database driven system you absolutely must consider migration at every step. 🆒

Strakos

A redesign is defiantly called for. We are already having problems creating reports and tracking orders due to orphaned records and repetitive data. There are also several instances of different data elements having the same name and tables without primary keys.

I was hoping to avoid migrating data to a totally new DB. I just went through that process with a COBOL DMS to ORACLE DB modernization project. That was no fun at all, besides being costly and time consuming. What I would like to do is redesign in a development environment, then incrementally make changes to the live site as I go. I'd then evaluate the impacts of each change, hopefully avoiding major problems.

Currently traffic is light on the site, and I don't foresee a surge in traffic until mid 2003.

Complicating matters is the fact I haven't done a lot coding, most of my background is system design, DB design, and requirements analysis. On the other hand, my programmers haven't had a lot of DB design experience; I’m having trouble convincing them of the need to at least have some constraints. Up to this point, tables have been added on an as needed basis with little regard for data integrity.

Any thoughts on this approach would be appreciated

[deleted]

" then incrementally make changes to the live site as I go."

I think you'll find that even more problematic, because you'll be fighting the bugs in the new code and incompatibilities between the old and the new code. This method only really works if you already have an OOP based script, or something very modular in design.

If the entire datanbase changes, then your entire code must change, so a re-write is probably easier anyway...

Sxooter

The only way you're gonna sell your programmers on constraints if you can convince them that they will make their lives easier, since they'll spend less time fixing buggy code to keep the data coherent.

Often times showing them how inconsistent the data currently ARE and then making them clean up their own messes can help motivate them towards a better understanding of database theory and why they should be using one the right way, not as a giant flat file with SQL attached.

You might find it helpful to teach them some solid, simple database theory, on things like race conditions, data coherency and all. The hardest part is convincing them that all the stuff they think they want to do in code should really be in the database.

Since you're looking at postgresql, it might help if you get them involved in writing stored procedures so they can still do the work "in code" just right next to the database instead of over a db connection.