Database Design – Linking Parent and Child Relationships

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.

All your requirements, save for #8, are implemented by relational databases (consider INFORMATION_SCHEMA, which is a standard relational database feature- the information schema contains views which describe the tables in the database, which columns they have, etc.- your app would work a lot with the INFORMATION_SCHEMA data).

Thanks to #8, you have basically cornered yourself into writing your own relational database engine.

It sounds like you are implementing Access which... implements its own relational database (Jet); it also sucks because implementing a relational engine is quite hard (more if you are subject to the constraints that Access has), and in the end, Access doesn't really make hard things easy.

Implementing a relational database engine on top of another datastore might simplify things, of course (i.e. Berkeley DB is useful for implementing an RDBMS, and Berkeley DB is everywhere. See a related answer I gave recently), but I would think hard about requirement #8 and the viability of your project.