First, I understand the reasons for not changing the schema at least on the first pass on this project, but I wanted to communicate my feelings regarding this as I feel I may be in the Minority?
My pie in the sky dreams for the next gen Tessitura project:
A fully relational, easy to understand / use and report off database schema.
and most importantly to me, unique IDs in each table with any given piece data existing in only one place where possible.
Consistantly named Field names, so that I can see a field name in one table and know what that is relating to and know it is only relating to one thing.
One that enforces data integrity and that has a simple, considered and consistent design for all parent child relationships.
In my 15 years of working in SQL databases, I have been used to seeing mapping tables and each table having a unique id column used to map relationships.
I know that for all the DBA's and power users out there, getting your data to a new schema could be bloody and difficult and learning a new schema would mean well...you'd have to learn a new shcema.
But I know that it would be easier in the long run, and would make the app run more efficently and be easier to report off and maintain, so I see it as worth the cost for us all.
I'll second the call for consistent field names and ID columns. I have great hope for the Services Layer as a vehicle for doing some things in a much more straight forward fashion which will take some of the pressure off of the database.
The notion of being able to access SSRS reports from an external client is particularly exciting.
I love what I saw in the V11 demo and the changes to the constituent model. I think its going to be a huge leap forward for how our organizations do business.