I've actually used your first approach with quite some success, but in a slightly different ways that I think would solve some of your problems:
Keep the entire schema and scripts for creating it in source control so that anyone can create the current database schema after a check out. In addition, keep sample data in data files that get loaded by part of the build process. As you discover data that causes errors, add it to your sample data to check that errors don't re-emerge.
Use a continuous integration server to build the database schema, load the sample data, and run tests. This is how we keep our test database in sync (rebuilding it at every test run). Though this requires that the CI server have access and ownership of its own dedicated database instance, I say that having our db schema built 3 times a day has dramatically helped find errors that probably would not have been found till just before delivery (if not later). I can't say that I rebuild the schema before every commit. Does anybody? With this approach you won't have to (well maybe we should, but its not a big deal if someone forgets).
For my group, user input is done at the application level (not db) so this is tested via standard unit tests.
Loading Production Database Copy:
This was the approach that was used at my last job. It was a huge pain cause of a couple of issues:
- The copy would get out of date from the production version
- Changes would be made to the copy's schema and wouldn't get propagated to the production systems. At this point we'd have diverging schemas. Not fun.
Mocking Database Server:
We also do this at my current job. After every commit we execute unit tests against the application code that have mock db accessors injected. Then three times a day we execute the full db build described above. I definitely recommend both approaches.
Best Answer
I have my own Delphi/MySQL framework that lets me add 'new screens' very rapidly. I won't share it, but I can describe the approach I take:
I use a tabbed interface with a TFrame based hierarchy. I create a tab and link a TFrame into it.
I take care of all the crud plumbing, and concurrency controls using a standard mysql stored procedure implementation. CustomerSEL, CustomerGET, CustomerUPD, CustomerDEL, etc...
My main form essentially contains navbar panel and a panel containing TPageControl
An example of the classes in my hierarchy
TFrame TMFrame - my derivation, with interface implementations capturing OnShow, OnHide, and some other particulars
--TWebBrowserFrame --TDataAwareFrame --TObjectEditFrame --TCustomerEditFrame --TOrderEditFrame etc... --TObjectListFrame --TCustomerListFrame
etc...
and some dialogs..
TDialog TMDialog --TDataAwareDialog --TObjectEditDialog -- TContactEditDialog etc.. --TObjectSelectDialog --TContactSelectDialog
etc...
When I add a new object to manage, it could be a new attribute of customers, let's say we want to track which vehicles a customer owns.
create table CustomerVehicles I run my special sproc generator that creates my SEL, GET, UPD, DEL test those...
Derive from the base classes I mentioned above, drop some controls. Add a tab to the TCustomerEdit.
Delphi has always the Dataset as the abstract layer, expose this to your GUI via DataSources. Add the dataset to the customer data module, and "register it". My own custom function in my derived datamodule class, TMDataModule
Security control is similarly taken care of in the framework.. I 'Register' components that require a security flag to be visible or enabled.
I can usually add a new object, build the sprocs, add the maintenance screens within an hour.
Of course, that is usually just the start, usually when you add something, you use it for more than tracking. If this a garage application, we want to add the vehicle the customer brought into the garage, id it so we can track the history. But even so, it is fast.
I have tried subcontracting to younger guys using 'newer development tools', and they never seem to believe me when I say I can do this all ten times faster with Delphi! I can do in two hours bug-free what it seems to take them two days and they still have bugs...
DO - Be careful planning your VFI! As someone mentioned, if you want to change the name of a component on one of your parent classes, be prepared for trouble. You will need to open and 'edit' each child in the hierarchy, even if you clean DCU you can still have some DFM hell. I can assure you in 2006 this is still a problem.
DON'T create one monster datamodule
DO take your time in the upfront design, refactoring after you have created a ton of dependents can be a fun challenge, but a nightmare when you have to get something new working quickly!