BigQuery中的数据库设计

Question

I have a main table called "Acquisition" with multiple columns thatwould be referencing other tables (ex: "Source", "Application", etc. - For example, "Source" would have multiple possible values that wouldbe used in multiple rows of the "Acquisition" table). What bothers mea bit is that the way is that the rows of the "Acquisition" tablewould return datas that would like this:

id > 1 ; value > 23.4 ; source_id > 1 ; application_id > 3 ;platform_id > 1 ; country_id > 1 ; etc.

Do you think there's another way to design it to make it more readable / user-friendly ?

Here's an extract of the code of the schema:

acquisitionSchema = bigquery.Schema {
    &bigquery.FieldSchema{Name: "id", Required: true, Type: bigquery.StringFieldType},
    &bigquery.FieldSchema{Name: "value", Required: true, Type: bigquery.FloatFieldType},
    &bigquery.FieldSchema{Name: "source_id", Required: true, Type: bigquery.StringFieldType},
    &bigquery.FieldSchema{Name: "application_id", Required: true, Type: bigquery.StringFieldType},
    &bigquery.FieldSchema{Name: "platform_id", Required: true, Type: bigquery.StringFieldType},
    &bigquery.FieldSchema{Name: "country_id", Required: true, Type: bigquery.StringFieldType},
    &bigquery.FieldSchema{Name: "adtype_id", Required: true, Type: bigquery.StringFieldType},
    &bigquery.FieldSchema{Name: "date", Required: true, Type: bigquery.dateFieldType},
    &bigquery.FieldSchema{Name: "download", Required: false, Type: bigquery.IntegerFieldType}   } 

sourceSchema = bigquery.Schema {
    &bigquery.FieldSchema{Name: "id", Required: true, Type: bigquery.StringFieldType},
    &bigquery.FieldSchema{Name: "value", Required: true, Type: bigquery.StringFieldType},
}

I thought of directly putting the value of the source, platform, etc. but it might get messy as I get my data from multiple sources through APIs unless I make all the necessary controls in my code.

Thanks !

Answer 1

Usually we do a RECORD that has two columns (id,name)

-country
 |id
 |name

this way in our query we can use country.id to query by integer, or country.name to display the value for quick inspection.

Since nowadays storage is cheap, we can afford storing the literal representation in every column. Since BQ is append-only by design, and we usually read most recent row, that already contains the fresh value if the name meanwhile suffered a change. Using LAST_VALUE function we can always pick the last record that holds the last name.