A foreign key is a column or set of columns whose values reference a key in another table, allowing related rows to be joined. In analytics warehouses, foreign keys connect new data sources to existing tables through shared dates, user IDs, or location keys. section 11.3.1
Reliable foreign-key relationships depend on practical data-design checks:
- Keep key values clean, accurate, complete, and stored in the same format across tables. section 11.3.1
- Map alternate identifiers to shared keys; for example, join customer emails to a unique user_id before wider analysis. section 11.3.1
- Check malformed records, data types, missing values, and data grain before joining or prejoining datasets. section 11.3.1
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
|
Use the normalized schema to identify primary and foreign keys, then trace relationships between entities such as customers, addresses, utilities, and customer activities. section 11.4.3
Foreign keys support restructuring normalized data into fact and dimension tables, including star schemas that make business events and entities easier for analysts and stakeholders to use. section 11.4.3
