Need a cheat sheet for foreign keys/dependent columns

[MichaelTiemannOSC]

The fixtures database gives examples of many uses of foreign keys, but I'm struggling to find the idiom that solves my problem.

I have a table that has three columns: FactorAreaCode (TEXT), FactorCode (INTEGER), and SubFactorCode (INTEGER) and three user-friendly columns: FactorArea (TEXT), Factor (TEXT), and SubFactor (TEXT).

I want to facet my table so users can pick a FactorArea and then a Factor and then facet by SubFactor so they can decide whether the available SubFactors are what they are looking for. (They could also pick a specific SubFactor if they want to see all data related to one particular metric).

I think the correct idiom is to create three tables (each with their own Primary Key aka PK):

1. FactorAreaTable (which links PK Code (INTEGER) to Description (TEXT))
2. FactorTable (likewise)
3. SubFactorTable (likewise)

Then in my main table, I can link the codes to the descriptions. But because SubFactors depend on Factors, and Factors depend on FactorAreas, maybe I want

1. FactorArea
2. FactorArea_and_Factor (PK FactorArea, PK Factor)
3. FactorArea_Factor_and_SubFactor (PK FactorArea, PK Factor, PK SubFactor)

What is the most idiomatic way to normalize data / facet in dataset when multiple columns which lead, step-by-step, to a compound primary key?

[MichaelTiemannOSC]

Answering some of my own questions with some successful experiences...

Labelled foreign key constraints are happiest when the foreign key in the child table is linked to a primary key in the parent table, and the parent table metadata has a label_column metadata declaration for the column that is to be displayed as the linked data.

Compound foreign keys break the kind of labeling I was hoping to get, because Datasette doesn't have the concept of showing a single column result with a pair or a triple of the corresponding primary keys as a kind of footnote. So what worked for me was:

FactorAreaCode (PK FactorArea_Code, FactorArea)
FactorCode (PK FactorArea_Factor_Code, Factor) <- note only one PK
SubFactorCode (PK FactorArea_Factor_SubFactor_Code, SubFactor) <- note only one PK

            "tables": {
                "FactorAreaCode": {
                    "label_column": "FactorArea",
                    "hidden": true,
                    "size": 10
		},
                "FactorCode": {
                    "label_column": "Factor",
                    "hidden": true,
                    "size": 10
		},
                "SubFactorCode": {
                    "label_column": "SubFactor",
                    "hidden": true,
                    "size": 10
		},
            },

This required me to shape my data from this:

FA_Code, F_Code, SF_Code

to this:

FA_Code, FA_F_Code, FA_F_SF_Code