I am designing data model in following way:
Table 1:
id: number
somedata: String
somedata: StringTable 2:
id: number
Table1_id: number
somedata: String
somedata: StringTable 3:
id: number
Table2_id: number
somedata: String
1st table is joined to 2nd table with one to many relationship. 2nd table is joined to 3rd table with one to many relationship. I am using java, JPA. I have to return JSON via rest api in following format:
{
"table1_id": 1,
"somedata": "somedata",
"table2_data": [
{
"table2_id": 1,
"somedata": "somedata",
"table3_data": [
{
"table3_id": 1,
"somedata": "somedata"
},
{
"table3_id": 2,
"somedata": "somedata"
}
]
},
{
"table2_id": 2,
"somedata": "somedata",
"table3_data": [
{
"table3_id": 3,
"somedata": "somedata"
},
{
"table3_id": 4,
"somedata": "somedata"
}
]
}
]
}
If I use mysql and above table structure I will need to use minimum 3 database calls to fetch from 3 tables separately and will have to construct my json. Is there a way where I can reduce database calls and also reduce effort to construct my json? I am open to change database(mysql) also. I just need fast, optimised and best solution. Note that table1, table2 and table3 has different fields so I can not denormalize them to single table.
I already have seen this question and my question is different than this: https://stackoverflow.com/questions/11047151/nested-json-from-3-one-to-many-tables