Chapter 7. Multi-Table Database Design: Outgrowing your table

image with no caption

Sometimes your single table isn’t big enough anymore. Your data has become more complex, and that one table you’ve been using just isn’t cutting it. Your single table is full of redundant data, wasting space and slowing down your queries. You’ve gone as far as you can go with a single table. It’s a big world out there, and sometimes you need more than one table to contain your data, control it, and ultimately, be the master of your own database.

Finding Nigel a date

Greg’s lonely friend Nigel has asked Greg to help him find a woman to date with similar interests. Greg begins by pulling up Nigel’s record.

Here’s Nigel:

image with no caption
contact_id: 341
last_name: Moore
first_name: Nigel
phone: 5552311111
email: nigelmoore@ranchersrule.com
gender: M
birthday: 1975-08-28
profession: Rancher
city: Austin
state: TX
status: single
interests: animals, horseback riding,
movies
seeking: single F

The interests column isn’t atomic; it has more than one type of the same information in it. He’s worried it won’t be easy to query.

Greg adds Nigel’s request to his TO DO list:

image with no caption

Why change anything?

Greg’s decided not to change the interests ...

Get Head First SQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.