How should I structure data if I want to data as drill down

[SLama]

Hello all, 

I have with data structure that looks like this where we have fac_id is within area_id and area_id is within dist_id. Can you can see dist_score is duplicated while area_score has some duplicates and fac_score has all distinct data for each fac_id. Should I split the table into 3 tables? How would you structre it properly so when I go to area_score or disct_id, I get it's data isntead of getting sums. 

Thanks in advance.

[v-jialongy-msft · Accepted solution]

Hi @SLama 

To structure your data for drill-down analysis, splitting the table into three related tables is a good approach. This will help you avoid redundancy and make it easier to query specific scores at different levels. Here’s how you can structure it:

District Table

DIST_ID	DIST_SCOR
1	0.7
2	0.9
5	0.88
6	0.2

Area Table

AREA_ID	DIST_ID	AREA_SCOR
1	1	0.1
2	1	0.6
3	5	0.22
5	5	0.35
6	6	0.6

Facility Table

FAC_ID	AREA_ID	FAC_SCOR
1	1	0.1
2	1	0.2
3	1	0.3
4	1	0.3
5	1	0.5
6	1	0.6
7	1	0.23
8	2	0.22
9	2	0.23
10	2	0.5
1	3	0.12
2	3	0.2
3	3	0.33
4	3	0.3
5	3	0.55
6	3	0.66
7	3	0.23
8	6	0.22
9	6	0.23
10	6	0.5

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.