3

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?

  • 1
    Your question is too broad. Wild-ass guess? EAV. However, tread carefully; EAV should only be used when absolutely necessary, and only for the use cases for which it is intended. – Robert Harvey Oct 30 '17 at 20:43
  • To make your question a better one, provide a specific example of your actual schema where the conditions you describe in your question hold true. – Robert Harvey Oct 30 '17 at 20:44
  • Yeah we need some hard examples to be of any help. I'm inclined to agree that this is too broad as-is to answer. Also I think "best practices"-type questions are frowned upon. That having been said-- the idea of creating one model to rule them all, especially when you have a complex and functioning legacy system, is nearly impossible. – RibaldEddie Oct 30 '17 at 20:46
  • 1
    Yeah, there's no general solution for this problem. You can use a document store, but it will probably suck to query. You can use EAV, and it will suck to query. You need to decide how your data are going to be queried and where the keys are. I understand you may need to cube the data or reorganize it. Many such systems have an OLTP schema for the read/write system and an OLAP data warehouse that might be somewhat out of date but is much easier to query because it's been reorganized. Again, though, there's no general solution other that to understand how your data are used. – Bacon Bits Oct 30 '17 at 21:40
2

While I haven't personally had the need, I have heard Domain Driven Design mentioned more than once in meetup talks by programmers for local research organizations with similar issues. Specifically, DDD talks about the need for bounded contexts and context mapping when modeling gets complex.

Bounded contexts are areas where the same words are used to describe similar yet ambiguous concepts. The example in the article I linked in the previous paragraph is a bank account versus an online banking account (the username and password you use to log in, etc.). You want to use the word Account to describe both of those things. They have many fields in common, like the same person, for example, but combining them into one Account object (or database table) would be a mistake.

These bounded contexts are identified with the help of domain experts. You might see dozens of ways to organize it, but they should be able to help you reduce that number significantly. I highly recommend getting the DDD book and learning the details from someone who knows a lot more about it than I do.

0

It's difficult to answer with the information given, but often when you have need for multiple ways to look at the same data, you may need some sort of OLAP (Online Analytical Processing) database.

Under OLAP, you'd create (relatively simple) tables to contain facts, then a series of dimensional tables that are essentially denormalized versions of the fact tables. The dimensional tables each allow a different way of slicing up the data; for example, time-dimensional table would denormalize the facts into a flat structure with time as a key. If you come up with a new way to look at the data, you could keep the fact tables and add a new dimensional table; for example, if you want to examine differences by user's age, you could have an age-dimension table. Data integrity is done via brute force; dimension tables will need to be generated and regenerated as the facts tables get updated. So this is not an approach you'd ever use for OLTP. But it might work in your case.

Not the answer you're looking for? Browse other questions tagged or ask your own question.