Real-world usage

I started a thread in the Feature Request section that morphed into some updates on how NORMA is being used at my work. Since the continued updates may be of interest, it would probably be a good idea to continue the discussion outside the tech support forum.

During the beginning of the project the NORMA-generated SQL was a tough sell because it wouldn't run directly in SQL Server 2008. Using some tips from this forum I modified the MS-SQL transform to match both the expected data types and the preferred capitalization and abbreviation settings. It took a few passes to get everything our DBAs wanted (and they're still not happy with the naming conventions for foreign keys or the inability to specify column order), but with a bit of effort they were satisfied.

Before adopting a fully model-drive approach, I'd print out the relational model and the DBA would comb over the structure by hand. Once satisfied, he would build the database with his tools according to his preferences. He'd print the logical schema and I'd go through that to update the conceptual schema with any changes that he made. A full round-trip of around two dozen tables could take over 8 man-hours, depending on the number of changes made.

Now, if they don't like the tables I make a quick change to the conceptual model and we run the generated script as-is. One section of the model has been mostly stable for several weeks, but there are constant tweaks as new questions arise. It's been an immense help that database changes are effectively "free".

The regular schema changes have brought up a new problem. We've outsourced the actual development of the project, and the developers don't have the experience with our business domain. They want a lot of sample data to make sure they understand what's going on. One of our lead developers has been charged with creating the data and scripting it so that it can be run whenever we need to refresh the database. Initially it was thought that the sample data script would take a couple of days. It's been several weeks now, and we're just barely getting data for all the basic use cases.

In our developer's defense, the schema has changed in one way or another at least twice a week. To complicate matters, the database that NORMA generates has a lot of constraints; considerably more than they're accustomed to seeing. All the foreign keys and mandatory constraints mean the data must be much more robust than adding a couple of rows to the table in question.

It's become apparent that sample data is a limiting factor. Not only is it necessary to communicate with the contractors, but any testing we want to do will require it. We've only modeled about 1/3 of the project, and it's obvious that this will be severe pain point. It should be noted that this pain would exist regardless of what modeling methodology we were using.