'How best to structure database with multiple possible join tables
I'm trying to work out how best to structure some database tables so I can ensure my response entities are as coherent as possible.
Basically say I have employees and I have different documents, that employee can have create a document of each type, however, those documents could be any one of 4 possible types of documents and the information and structure of those documents is completely different from one another... How would be best to structure that to get a nice response?
Of course I could do 4 many-to-one joins with a table called employee with 4 document tables called document_a, document_b and so on joined on via an employee_id column... but this would be somewhat messy in a JSON response as it would be akin to:
{
{
"firstName": "first",
"lastName": "last",
"documentAs": [
{
"field1":"test",
"field2":"test"
}
],
"documentBs": [
{
"field3":"test1",
"field4":"test2"
},
{
"field3":"test3",
"field4":"test4"
}
],
"documentCs":[],
"documentDs":[],
}
}
Ideally though, I would want the JSON response to be closer to the following:
{
{
"firstName": "first",
"lastName": "last",
"documents": [
{
"documentType": "documentA",
"field1":"test",
"field2":"test"
},
{
"documentType": "documentB",
"field3":"test1",
"field4":"test2"
},
{
"documentType": "documentB",
"field3":"test3",
"field4":"test4"
}
]
}
}
Is there any realistic database structure that could allow me to do get this kind of response as I can't think one of due to their different table structures? Of course I could do the 4 many-to-one joins and manipulate the data afterwards into the structure I want but that would be bad performance-wise and resrict filtering and possibly other functionality. To be clear, the fields in each document would be fixed, not dynamic, but unique and different to each document type.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
