2

This is a design/planning question, not specific to any particular software/deployment. The stuff I'm talking about doesn't exist yet, but I am hoping to avoid mistakes early in the process.

Here's the situation that I'd like advice on:

  1. We have 7 business "verticals" (e.g. Sales, marketing, etc) with each vertical separately maintaining its data and a RESTful API that they use to allow access to their data.
  2. The raw data is separately maintained by the respective vertical. This gives each vertical more freedom to define their architecture, data pipeline, and processing.
  3. The API for each vertical is used to define a higher-level "contract" that is supposed to remain invariant (syntactically and semantically) regardless of changes to the underlying data architecture.

Here's the problem:

We like the idea above because it decouples each business unit. However, the decoupling is also a problem --- since we are part of a single business, we realized that we share a common data model for a large subset of our data.

For example: potential project sites are tracked by the marketing team then pursued by commercial team as an opportunity, then lost/won by sales, then designed by engineering, and maintained by services.

Each vertical tracks different things about these entities and they may not have one-to-one cardinality (e.g. Multiple opportunities per potential project).

Another example is enforcing naming constraints: we have a common set of names for competitors, models, countries, etc. and we want to enforce this across our datasets so, for example, "Acme X-35" is the only way to describe this make and model across all our datasets.

my ideas

Addressing connections between data models (meta model)

Either we enforce cross-database data model "by convention" (seems brittle) or we create a "meta database" that pulls from each API into a relational database composed of "views"...we just create and relate a bunch of materialized views (no raw tables). This database would contain normalized fields and tables that implement the common model.

Enforcing naming constraints

The key assumption is that names are immutable after we decide upon them. Assuming this holds, then we can provide a simple "validation server" that serves the allowable names for a field (a RESTful lookup table, essentially) and each database can incorporate this into their validation workflow.

Ok so, as you can see, I've tried to give this some thought but I'm not sure if there is a more standard way to coordinate and synchronize cross-database data model with constraints.

  • 2
    Using Globally-Unique Identifiers (GUID's) as primary keys would be a good start. – Robert Harvey Aug 17 '17 at 16:24
  • @RobertHarvey thanks! I think this will help with the data model issue quite a bit. – user209881 Aug 17 '17 at 17:01
  • Your first requirement change will be to make names mutable. – Pieter B Aug 28 '17 at 16:36
  • All departments (verticals) basically use the same business entity, the project in your example. Is it not required to have a company wide shared entity to prevent each department form inventing their own information structure and incompatible business rules? – Kwebble Aug 28 '17 at 20:22
1

In general it's a bad idea to try to enforce this accross databases. You already said that each business unit has it's own architecture. So as long as every one knows who owns what piece of data then they should be 'forced' to follow that system.

Which department decides what products you have. That department will have an api that contains a list of current products. If that department wants to add more fields it should be little problem to do so.

Also remember that an engineering system doesn't track opportunities. Especially not opportunity's that are lost. Engineering only cares about bids you actually won. So the insight here is that an opportunity is not the same thing as an signed contract. (Even if they are both called Acme X35)

Of course each system can publish it's own api (possibly formalized with for example a wsdl). That way each department can expand the way that's relevant to them.

And of course nothing beats just talking to eachother on a regular basis about how you want to change things.

0

RDF is a better match to this than SQL, because it allows links to another database by design. There are some performance hits with triple stores, especially large ones, but if you can afford this it is an option.

An RDF schema is expressed in RDF and can naturally import definitions from another schema. So you can have the benefits of a data warehouse without a separate store.

  • 2
    RDF looks like a web standard. Are RDF and SQL really mutually-exclusive? – Robert Harvey Aug 28 '17 at 16:42
  • You can serialise results from a relational database as RDF. But that won't give you the flexibility of schemas that you probably need. It will give a way of communicating associations across databases, but will leave to you the problem of identifying them (unique identifiers). Actually using a triple store and RDF schemata would solve these problems. But generate others of course, it's always a trade off. – Hugh Morris Sep 4 '17 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy