Imagine a simple table containing contact information. It has two places for phone numbers (
Phone2). Let’s say you wanted to split those off into a related phone numbers table so you could support many more numbers for a single contact. As part of that process, you’d need to create two rows for each existing contact– one for each phone number.
One way to get two phone numbers is to handle each phone number as a separate process, combining a list of all contacts and their first number with those same contacts and their second number. So you’d
SELECTeverything from the table once for the first phone number and then
UNIONit with another
SELECT. This works, but if you have a large list of columns or a complex set of
JOINs, you end up repeating a lot of code.
Instead, what if you simply, magically double the rows and then pick and choose the columns you wanted from each of the rows?
Tally tables are a great “Get out of a bad design (relatively) cheap” tool. They’re not something I use on a daily basis, but they’ve made life easier for me on dozens of occasions.