Been thinking about this. It needs to do a few things:
- represent the data model schema in an abstracted way, that is, we really don’t want raw SQL in here, although we may have to allow an opt-out for some twiddly bits that can’t be easily otherwise represented
- support a back-end to produce an SQL DDL for the data model (for initializing a PostgreSQL database with the schema)
- support a back-end to populate the user interface database with a description of the schema (since the user interface won’t currently talk to the internal system catalog of PostgreSQL)
- support a back-end that can produce a “changes SQL DDL” that will alter table structures from one particular release of the model to another (and the complementary user interface data) so that we can implement something akin to the develop/testing/stable LCFG release mechanism
- provide a modular approach to the data model – such that different bits can be combined together and function independently of the whole
After some thought it looks like the best way to do all of the above will be to use XML-Schema as the representational format in a multi-file structured/validated Subversion repository with some back-end hooks to do the raw SQL creation. More details on the overall structure and how releases will be managed shortly.