You are viewing a public preview
Sign in to save your progress, do assignments, join discussions and get feedback.
Tabular data organization; tables, fields, records and data types; primary and foreign keys; levels of detail; one-to-one, one-to-many and many-to-many relationships; structuring data for analysis.
Learning outcomes
The lecture slides for this lesson are below.
By the end of this lesson you can:
Key idea: Reliable charts start with well-structured data. If you know what one row means and how tables link through keys, you avoid double counting, missing records and wrong totals.
Business data come from many operational systems (sales, customer management, inventory), and these may use different names, formats and levels of detail. Good organisation means identifying:
Poor structures lead to double counting, incorrect aggregation, missing records and inconsistent results.
A database contains related tables, and each table describes one entity or one kind of business event. Rows are records; columns are fields (attributes). The slides use the Orders table of Sales_Data.xlsx as the reference: Row ID uniquely identifies each row (the primary key), while Customer ID and Product ID are foreign keys that link to the Customer and Product tables.
Each field needs a consistent data type. Tableau uses String, Number (whole or decimal), Date and Date & Time, Boolean, and geographic roles for fields such as Country or Province. A wrong type causes problems later: a date stored as text cannot be put on a time axis, and a number stored as text cannot be summed.
The level of detail (or grain) is what one record represents. In the course Orders table, one row is one product line of an order, so one order with three products has three rows. The Customer table has one row per customer, the Product table one row per product, and the Targets table one row per Region and month.
Grain decides how you may aggregate. Summing Sales over Orders rows is correct. Counting rows gives the number of order lines, not the number of orders; for orders you count distinct Order ID values.
| Type | Meaning | Example |
|---|---|---|
| One-to-one (1:1) | Each record matches at most one record in the other table | Employee and employee's company car record |
| One-to-many (1:N) | One record links to many records | One customer has many order lines |
| Many-to-many (N:M) | Many records link to many in both directions | Products and promotions, where a product can be in several promotions and a promotion covers several products |
Before combining tables, check four things: the key has the same meaning in both tables, the data types are compatible, the values are standardised (no "C-001" versus "c001"), and the key is unique on the "one" side. A duplicated key on the "one" side silently multiplies rows.
Joins and relationships combine columns from different tables; a union adds rows.
Model the four tables of the course dataset before building any chart.
Flashcards · Data Structure and Data Models
9 wordsA field whose value uniquely identifies each record in a table.
A field in one table that refers to the primary key of another table.
What a single record in a table represents; it determines how measures may be aggregated.
One record in a table links to many records in another table.
Many records link to many records in both directions, often needing a bridge table.
Keeps all records of the left table and matching records from the right; non-matches get NULL.
Appends records of tables with compatible structures row by row.
Restructures data between wide format (values in columns) and long format (values in rows).
Inflated totals caused by duplicated rows after joining on a non-unique key or mismatched grain.