I'm aware of some general best practices when designing a database for an application, but what about redesigning?
I'm on a team tasked with re-designing an internal business application, though despite me saying "internal," I'm unfortunately many, many layers of people away from contact with the actual users of the system.
The current program is in Oracle Forms, scattered across a bunch of non-normalized tables, sometimes with multiple near-duplicate tables holding slight variants on each others' data. The constraints are often in the form of poorly-enforced stored procedures. Even the types don't seem to be stored right. I've encountered all sorts of bad data that Oracle seems to ignore but gave fits (and rightly so) to SQL Server's Import/Export Wizard. (For example, two digit integers do not constitute a complete datetime!)
The original program probably goes back twenty years, and all of the original developers have retired so long ago that even the older people here have no idea who they were. As a result, there also aren't really any clean requirements to go off of–we're just supposed to duplicate the existing application's functionality and keep its existing data.
The end result of the rewrite is going to be a web-based version running on ASP.NET with MS SQL Server for the back end.
My other two developer teammates are much, much older than me, both with business/MIS backgrounds whereas mine is CS. The senior member's experience has been almost exclusively Oracle forms and the other member has mostly done business applications work in Visual Basic. Although my database background has been limited to designing new databases for projects in MySQL or SQLite, mostly for my undergrad classes, I seem to be the only one with experience actually designing databases at all.
I've already written a little program in C# that reads in all the existing data to a neutral format, ready to be re-cast and placed into a new database. I plan to write the load-in code after the destination database is designed, so that data can be properly split across the new normalized tables, added in the correct order to follow new constraints, etc. The same program could then be run again later to copy the production data to the real newly deployed finished redesign. This leaves the actual redesign of the database as the main thing to figure out.
So the heart of my question: what are some best practices for doing a redesign from the database level up of an existing application?
Best Answer
I think you already know how to normalize a database.
What you need are strategies for minimizing the risk when moving all of the software to the new database.
What I'm suggesting is more work as a trade-off for less risk.
Normalize the database, and create a process to populate the normalized database with data from the original database. The original database will be the database for inserts, updates, and deletes. The normalized database will be the query database only during the conversion.
Your populate process will have to run as often as the need for query data. If day old data is acceptable, you can run a nightly populate process. If you need more current data, you have to run a continuous populate process.
Build the query portion of your new ASP.NET system, pointing to the new normalized database.
The query results from your new system should compare with the query results from the original system.
You could stop at this point. That's a business decision, not a technical decision.
At your leisure, you create new insert / update / delete functionality in your new ASP.NET system. As you create the new functionality, you turn off the parts of the original system that correspond. At some point, nothing of the original system remains.
The advantages of converting in this manner are reducing risk by building the query portion first. Generally the query functions are simple compared to the business logic embedded in insert / update / delete functionality.
You convert the insert / update / delete functionality one process at a time. If there's a problem with misunderstanding the business logic, it can be fixed while your users are using the original system.
It should go without saying that your populate process better be absolutely consistent.