Database Design Book: Learn how to get from business requirements to a database schema

Introduction....8

Who is this book for?....8

What level of understanding is this book aimed at?....8

What you would get from reading this book....9

Who I am....10

Building a logical model....11

System requirements as input....11

Who do we write the logical model for?....12

For yourself....12

For software developers....13

For the project group....13

Can I skip the logical model?....14

Can I use an ERD instead?....14

Elements of a logical model....15

The process....15

Anchors: introduction....16

Example: posts....16

A list of anchors....17

Example: invoices....17

Anchor IDs....19

Attributes: introduction....20

Attributes: definition....20

Attributes and anchors....22

Human-readable questions....22

Example values....22

Data types and types of data....23

What is not an attribute?....24

How to confirm that all the attributes have been listed?....24

Links: introduction....25

Links: definition....25

Links: pair of anchors....26

Links: cardinality....27

Cardinality is a business concern....28

Links: sentences....29

False links and unique pairs of IDs....30

More on anchors....31

Unique attributes....31

Optional unique attributes....33

External IDs....33

External ID enumeration....34

Several unique IDs....35

Implementing physical IDs....36

Handling time in the logical model....36

Hello world'' use case: podcast catalog....41

Business requirements....41

Anchors....43

Attributes....43

Links....46

Cross-checking the requirements....50

A diagram....51

Are we there yet?....51

Evolving the system....52

Building a physical schema....53

Many table design strategies are possible....53

Table-per-anchor table design strategy....55

Action plan....55

Anchors: choosing table names....56

Attributes: choosing column names....57

Attributes: choose column data types....59

Recommended physical types for SQL databases: summary....61

Strings....62

Integer numbers....63

Monetary amounts....63

Numeric values....63

Yesno values....64

Eitheroror values....64

Dates....65

Date with time in UTC timezone....65

Date with time in a specific timezone....65

Timezone names....65

Binary blobs....66

Links....66

One-to-many (1:M) links....66

An ID cannot be an attribute value....67

Many-to-many (M:N) links....68

Podcast catalog: a complete physical schema....70

CREATE TABLE statements....73

Audio file and cover images....74

Physical ID design....75

Case study: a tiny CMS....75

The maximum number of items....77

Reaching the maximum number of items....77

Space taken by IDs....78

Disk space is time....79

Storage density....79

UUIDs as anchor IDs....80

Countries, currencies, languages: well-known anchors....81

Countries, currencies, and languages in your business....82

Handling time in the physical model....83

Other table design strategies....86

Table design concerns....86

Is there a recommended table strategy?....87

Table-per-anchor, revisited....88

Side tables....89

Naming and composition of side tables....90

A stopgap table....91

JSON columns....92

Dealing with absent data....95

Use case: user's bio....95

Sentinel values: NULL....96

Other sentinel values....97

Sentinel values exist only on a physical level....98

Explicit reasons for missing data....99

Summary....101

Secondary data....103

Cached column example....104

There is no free lunch....105

Cached column is not an attribute....106

Discovering secondary data....106

Evolving your database....108

Elementary database migrations....109

Table rewrite....110

Adding an attribute....113

Step 1: Update logical model....114

Step 2. Run database migration....115

Step 3. Update code....115

Dealing with table rewrite....115

Movie tickets: repeated sales pattern....118

Business requirements....118

Per-department modeling....119

Movies department....121

Maintenance department....121

Movie schedule department....123

Tickets department....125

A diagram....128

Conclusion....128

Books and washing machines: polymorphic data pattern....129

Business requirements....129

Per-department modeling....130

Key insight: generic anchor vs specific anchors....130

Multiplexing on item type....132

Tangled links....133

A diagram....134

Table-per-anchor approach....135

Polymorphic table design strategy....136

JSON-based columns....136

Physical schema....136

Storing links in JSON....137

Table design concerns, revisited....138

Documenting physical storage....138

Practicalities....142

Document-based catalog....142

Spreadsheet-based catalog....142

How much to write....144

Lightweight designs....146

Learn how to get from business requirements to a database schema

The goal of this book is to help you get from a vague idea of what you need to implement (e.g.: “I need to build a website to manage schedule and instructor appointments for our gym”), to the comprehensive definition of database tables.

To achieve the goal, first we show how to build a structured list of anchors, attributes and links. To understand the business requirements, we spell out what each element does, using carefully designed formalized sentences. After that, we can build a physical model based on the logical model, in a straightforward way.

• database design case studies;
• SQL;
• classic relational modeling topics;
• graphical notation.