0

I have a lot of tables like the following tables.

main_table

id |customer_id | tabl2_id  | tabl1_subJson_id |...
---|------------|-----------|------------------|------
1  |      1     |   1       |        2         |...
2  |      1     |   2       |        0         |...
3  |      2     |   3       |        1         |...
4  |      3     |   3       |        3         |...

tabl1

id |customer_id(unique) | json_column1                                         |
---|--------------------|------------------------------------------------------|
1  |      1             | {"1":['a','b','c'],"2":['e'],"3":['f','g']}          |
2  |      2             | {"1":['h','i'],"2":['j',,'k'],"3":['l','m']}         |
3  |      3             | {"1":['n','o','p'],"2":['q'],"3":['r','s','t','v']}  |

table2

id |   name   | ...
---|----------|-----
1  |  myName1 | ...
2  |  myName2 | ...
3  |  myName3 | ...

I need an output like below table results

id |customer  | tabl2_name|  tabl1_subJson   |...
---|----------|-----------|------------------|------
1  |   jack   |  myName1  |        c         |...
2  |   jack   |  myName2  |        e         |...
3  |   lisa   |  myName3  |        k         |...
4  |   karl   |  myName3  |        v         |...

and i have other questions: Tables like tabl1 are better or tables like the following tables?

tabl_a

id |   name   |       sub_name       |  ...
---|----------|----------------------|------
1  |  myName1 |   ['a','b','c']      |  ...
2  |  myName2 |   ['e']              |  ...
3  |  myName3 |   ['f','g']          |  ...
...|  ....    |   ...                |  ...
12 |  myName2 |   ['mm','dn']        |  ...

or table_b

id |   name   | sub_name |  ...
---|----------|----------|------
1  |  myName1 |   a      |  ...
2  |  myName1 |   b      |  ...
3  |  myName1 |   c      |  ...
...|  ....    |   ...    |  ...
12 |  myName2 |   mm     |  ...

If there are one million users, the number of records in the tables will be as follows:

tabl1 = 1,000,000

if the name column has 12 constant names: tabl_a = 12,000,000

if each user has an average of 20 sub_name: tabl_b = 240,000,000

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.