I’m working in a project that is building a system to manage data (10-20 million records) collected by a research organization. One of the challenges is that even though the data is superficially similar across the organization (having reasonably similar set of fields), there are currently 20-30 different databases used to manage it, and maybe a dozen ways to organize it. The system being built should gradually replace all of these.
The idea has been to create a single conceptual data model and database schema which would be flexible enough to handle all the different ways to organize the data. Entities in the model form a network (not a strict hierarchy), and a single piece of data would only use some of the entities and relations between them. There wouldn’t be a single central concept of a “databased object” that would be shared by all.
I’m finding the resulting model difficult to understand, develop and explain to users, since there are so many possible ways of using and expanding it. I also feel that having a single model and a database schema creates a false sense of consistency, without making people think whether it is actually valuable to have so many different ways to handle and store the data.
I started thinking that instead of a single flexible model and database schema, maybe we should only define the entities and attributes, and have several models describing alternative ways how they can be grouped. And instead of creating a relational database with alternative ways to link the tables, creating a document database which would allow several alternative hierarchical document schemas.
Are there best practices on modeling and databasing such data that can be organized in very different ways, despite having mostly shared set of attributes?