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.
Thanks to you both for the comments.
First of all, I would say that if our development department had its way we would have totally redesigned the schema, keeping what's already good and consistent and fixing up a lot of inconsistencies. But in the real world we have to face the fact that 300+ organizations are using the software and they have developed and are using countless custom reports and screens.
So we've decided to take a more conservative view here. In any area of the system that we're adding major functionality we are following more standard conventions, including the naming of columns. You will also see much more use of database constraints to ensure internal integrity. As you point out, this is a big requirement when we want to be able to open up the database to more outside consumers.
And you will also see many more tables having surrogate primary keys as you describe. Because we are using Hibernate as an ORM (object relational mapping) system, the use of those keys is vital. More importantly the RESTful paradigm that we are adopting for our services relies on this for resource identification. Part of the beauty of REST is the ability to use the four main HTTP verbs (GET, PUT, POST and DELETE) to take actions on resources addressed like:
https://localhost/ConstituentService/Addresses/4
where 4 is an address id. Or:
https://localhost/ConstituentService/Addresses/?ConstituentId=2
where 2 is a constituent ID and you want a set of addresses for that constituent.
Please keep up the comments and questions!
Chuck,
Thanks for the additional information. That is all good news. And I had plenty of concerns about reports and custom screens having to be rebuilt all in one shot, so I am grateful that you have chosen a middle road.
I haven't done much actual coding in my life, but I've studied Model-View-Controller methods for some time and they sound very close to the architecture for NextGen will be. The URL examples you mention make me think that my PHP/MySQL hobby time might actually pay off down the road :)