A primary key is a field, or combination of fields, that uniquely identifies each record in a dataset or table. It provides a stable way to connect related data. section 2.1.2 section 11.4.3
Use primary keys to assess whether data can support an analysis and to design well-structured analytical models:
- Check whether separate datasets share a key for the same user or customer before attempting to combine them. section 2.1.2
- Identify primary and foreign keys in a normalized schema before proposing fact tables, dimension tables, or a star schema. section 11.4.3
- Treat key availability as part of the dataset’s analytical scope, alongside aggregation, timestamps, missing fields, and data quality. section 2.1.2
Table 11.3 Normalized schema containing available tables collected by the startup
|
Table name
|
Description
|
Columns
|
|---|---|---|
customers
|
Individuals or businesses who have purchased a product or signed up for the utility cost-saving service.
|
id, first_name, last_name, email, signup_date, customer_type_id, utility_id
|
customer_types
|
Type of customer, such as a subscriber to the utility service or purchaser of a product.
|
id, type
|
customer_activities
|
Customer sign-in counts per day.
|
id, customer_id, day, n_logins, n_activities
|
addresses
|
Customer addresses where utilities are being monitored for the utility service. There can be multiple addresses per customer.
|
id, created_at, customer_id, address, zip_code, country
|
products
|
Products sold, such as solar panels and thermostats.
|
id, created_at, name, type, price
|
utilities
|
Utility companies used by customers who signed up for the utility service.
|
id, name, created_at
|
utility_readings
|
Monthly utility usage readings for each customer.
|
id, month, utility_id, customer_id, usage, cost
|
Inspect the normalized schema to locate candidate identifiers such as customer IDs and relationship fields, then trace how tables can connect. section 11.4.3
