Hi Adrian,
> On 07/11/2021 18:38 Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> My questions for this are:
>
> 1) How stable are the core objects?
Hard to tell. A lot of changes were necessary for the prototype, as
expected. It's considered stable by some team members, who unfortunately
lack experience in database design and software development.
My main fear, from experience, are breaking changes to unique keys, e.g.
additional columns, that are likely to create issues in dependent views and
queries.
> 2) How divergent are the customer specific requirements?
Different projects may use a different subset of objects. I don't mind
having unused objects, e.g. empty tables, in the database schemas.
I still have to find out what is expected to differ between projects. But
it's impossible to anticipate every project and I bet there will be projects
that won't fit our model and require breaking changes.
One thing I already know that may differ is how modelled objects are named
in the real world. We deal with plots of land, hence the geographic data.
Depending on the region, these plots may use different naming schemes
relevant to legal documents. The core objects would use a generated
surrogate key but each project would have a dedicated relation that maps the
real-world names of those plots to their surrogate keys. The real-world
names can be normalized into multiple attributes instead of just storing
names as text. This normalization may vary between projects, e.g. different
number of attributes.
> 3) Given 1) and 2) would it be possible to craft extensions that where
> not customer specific?
That's the goal. The core should not contain any project specifics.
Doesn't need to be Postgres extensions because of the issue with pg_dump
omitting the extension version which I described in my original post.
I'm thinking about something like merges in Git. Database schemas for
project-a and project-b are like separate repositories and changes in core
are merged as necessary. This along with project-specific changes ("o" in
the diagram below) should result in a sequence of migrations applied to the
respective databases.
project-a oo--o--ooo-o---o--oo---->
___/ _/ _________/
/ / /
core o--oo-o---o-o--o-->
\ \__ \
\ \ \
project-b o--ooo--o-o-o--ooo----->
These merges (always from core to the projects) form a directed acyclic
graph from which the migration sequence can be generated using a topological
ordering.
> 4) Are you using or have you looked at Sqitch(https://sqitch.org/)?
Already looking into it after stumbling across it during research on the
mailing lists ;)
Erik