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?