Data architecture: the A, B, C you should understand
How companies store data is a mess. There are "chaotic organisations" and organisations with "commitment phobia".
Let me translate this into something you'll understand: MONEY
Every time you waste the chance to work with data properly, you're losing money.
What is data architecture?
It's the art and science of structuring, storing and managing data so it flows coherently, stays secure and becomes useful.
It involves connectivity, transformation and information security.
The architect's role: bridge between what the business needs and what's technically possible.
What is a data model?
A unique, structured representation tailored specifically to each company. Not generic. Not off-the-shelf.
Characteristics of a good model
- Multidimensional: looking back, present day, looking ahead.
- Shaped by the end goal.
- Scalable.
Hands on - online shop example
Step 1: Base tables
Products table: Product ID, Name, Category, Price, Stock.
Customers table: Customer ID, Name, Email, Phone, Address.
Orders table: Order ID, Date, Customer ID, Product ID, Quantity, Total.
Sample data
Products:
| ID | Name | Category | Price | Stock |
|---|---|---|---|---|
| P001 | Basic T-shirt | Clothing | €12.99 | 100 |
| P002 | Wool Hat | Accessories | €8.50 | 50 |
| P003 | Headphones | Electronics | €29.99 | 30 |
| P004 | Hoodie | Clothing | €25.00 | 75 |
| P005 | Casual Backpack | Accessories | €19.99 | 40 |
Customers:
| ID | Name | Phone | Address | |
|---|---|---|---|---|
| C001 | John Smith | john.smith@email.com | 600123456 | 123 High Street, London |
| C002 | Mary Johnson | mary.johnson@email.com | 600654321 | 45 Sun Avenue, Manchester |
| C003 | Luis Brown | luis.brown@email.com | 600789012 | 20 River Lane, Edinburgh |
| C004 | Anna White | anna.white@email.com | 600987654 | 5 Seafront Walk, Brighton |
| C005 | Carol Green | carol.green@email.com | 600432198 | 7 Main Square, Bristol |
Step 2: Normalise
Consistent format, uniform structure, input validation.
Step 3: Connect tables
Link them via unique IDs. Use VLOOKUP / INDEX+MATCH.
Formulas:
=INDEX(Customers!$A$2:$A; MATCH(B1; Customers!$B$2:$B; 0))
=INDEX(Products!$A$2:$A; MATCH(B2; Products!$B$2:$B; 0))
Step 4: Build a form
Interface for order entry with dropdown lists and dynamic lookups.
Step 5: Automate
Macros or Apps Script to transfer data from the form into the orders table. (Full script in the original post.)
The point
Work less, work better: automate processes, reduce errors, have clear information to make decisions.
Resources
- Downloadable Google Sheets file (linked in the post)
Relevant links
/en/services/guia-de-arquitectura-de-datos-en-empresas//en/services/arquitectura-de-datos//en/services/guia-del-curso-google-sheets//en/services/guia-del-curso-google-apps-script/
CTAs
- Newsletter signup form
- "Book my appointment" - 30 min networking call
