To be clear, I mean to ask this question from a strictly "Data Modelling and database-design, including referential-integrity" PoV.

I am currently designing a database that will serve as an aggregation service. However, our third-party data partners will not be providing us with data dumps. Rather, we need to be able to reference their data, and direct users to appropriate locations and queries to be able to retrieve the data they need directly.

Here's a simple example of what I mean:

Suppose that we are partnering with schools. Ecah school has information about students, teachers, courses and subjects such as those in this diagram: enter image description here

Our goal, in this example, is to provide a central resource for information about students and teachers and courses and subjects for the entire school district. We are not allowed to simply copy all of the data from each school, as this information is sensitive. So, we would like to be able to reference the data that is stored at individual schools, so that users can get the information that they need easily, directly from the source.

So, in our example, suppose that we cannot have any of the Student data, but we are allowed to have all of the Subject data. Also, we know the data model as shown in the picture. Suppose that a user comes to us and asks "what students are taking the subject Calculus I?" We cannot tell them this directly, because we do not have access to the Student data. However, using, say, a Schools table, and a table that references Subjects per School, and the metadata from the Data Model, we can provide links to School portals that allow users to get the Students taking that Subject from each School. Conceivably, we could also provide queries to execute, or even provide a mechanism to execute queries to access data about all of the Students from all of the Schools, and we would never actually have read access to any of the Student data.

Note that our data model is different from the original single-school data model in several ways. We have a School table. We have a School2Subject many-to-many relationship - so, our Subject table will need a SchoolID, for example. We also need to have data for linking to resources or for creating queries from a data model.

The Data Modeling Question:

Given a data model, and a subset of that data model that I am allowed access to, how can I go about methodically designing a referential data model that allows me to query the ontological relationships to the rest of the original data model? Is there some database modeling theory that would help me plan this? Are there algorithms that can help me design such a data model so that, for example, my referential data model can be automatically updated to reflect changes in the original data model?

  • You are talking about the logical data model, apparently, that doesn't concern itself with the physical location or representation of the data, so your question is moot. As for tools, it's a separate question and is off-topic on this site.
    – mustaccio
    Commented Apr 27, 2023 at 22:55
  • @mustaccio I am not talking about the logical data model. I need a physical data model, because I will have an actual database. My database, however, as in the example, will not manage students and teachers: my database will manage data sources that provide information about students and teachers. My question relates to structured methodologies for creating a schema to organize and query a database of schemas, in a sense.
    – Him
    Commented Apr 28, 2023 at 4:05
  • @mustaccio as to your comment about tools: I don't necessarily need the tools. An algorithm name or description will suffice, and I can find or implement the tools as needed. I have edited my question to incorporate your feedback.
    – Him
    Commented Apr 28, 2023 at 4:08


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.