To Embed or Reference - MongoDB Applied Design Patterns - page 4

Unfortunately,usinga LIKE clausethat'snotaprefixmeansthatthisqueryrequiresafulltable
scan to be satisfied.

Alternatively, we can use multiple columns, one for each phone number, as shown in
Table 1-3 .

Table 1-3. Phone book v2.1 (multiple columns)

id name phone_number0 phone_number1 zip_code

1 Rick 555-111-1234 NULL 30062

2 Mike 555-222-2345 555-212-2322 30062

3 Jenny 555-333-3456 555-334-3411 01209

In this case, our caller ID query becomes quite verbose:

SELECT SELECT name FROM FROM contacts

WHERE WHERE phone_number0 = '555-222-2345'

OOR phone_number1 = '555-222-2345' ;

Updatesarealsomorecomplicated,particularlydeletingaphonenumber,sinceweeitherneed
to parse the phone_numbers field and rewrite it or find and nullify the matching phone num-
ber field. First normal form addresses these issues by breaking up multiple phone numbers
into multiple rows, as in Table 1-4 .

Table 1-4. Phone book v3

id name phone_number zip_code

1 Rick 555-111-1234 30062

2 Mike 555-222-2345 30062

2 Mike 555-212-2322 30062

2 Jenny 555-333-3456 01209

2 Jenny 555-334-3411 01209