The Best Way to Store Phone Numbers in an SQL Database

[@techguy19]

Do you think it is better to have an enum attribute for different types of phone numbers? or create a phone_type_id attribute which links to a phone_types table? Thanks for the video, super helpful!

[@DatabaseStar]

Good question. You could use an ENUM but I don't think ENUM is supported in all databases. If it's supported in the one you use, that's good. An ENUM is a similar approach to using a check constraint. It could work if you don't plan on changing the values very often (which for phone number types is probably true). You could have the enum of Home, Business, Work, Mobile or whatever the values are. The drawback is that it's a little harder to change the possible values (you'd need to run an Alter Table statement I think). Also you won't be able to populate the values in a dropdown box in a user interface using the same list, but that may be ok. Having a separate phone_type table and ID may be the better approach for that reason (you can update the values, and show them in a dropdown list). But either approach can work.

[@xBiggs]

If you are storing the phone number as text, you should probably ensure that it only includes numeric characters.

[@DatabaseStar]

Great point! I would recommend for this to be done using a Check Constraint on the column. There are other ways to do this including validation in the application.

[@PaoloOrsato]

Hi I have a question. All around the internet they say that storing as an Integer is a no-no. I want to store hundreds of millions of numbers in the database and perform duplication checks on them. Wouldn't using integers be better here as the database would require less resources/time to duplicate check on them considering that i'm talking about hundreds of millions of rows ? Btw I wont use formatting or special characters or leading zeros. Its all numbers only

[@DatabaseStar]

Good question! I'm not sure whether it will be easier for the database to check for duplicates on numbers or varchar fields. I am guessing the numbers would be faster... but it could be worth running a test on them. You could store a smaller amount of them as varchar, and then remove duplicates, and see how long it takes. Then try the same thing but store it as number and see how long that takes. You could run this experiment on 100,000 rows to compare the performance.

[@jimshtepa5423]

Can you also show best practices for storing finacial transactions please? Eg money owed to and borrowed from contragents

[@DatabaseStar]

Sure, that’s a good idea.

[@AhmedShoulah]

What if the customer has more than one phone number should I store it in separate table using customer_id as foreign key? or create one large table with customer name, phone1, phone2, phone3, phone4 , phone5 ??

[@DatabaseStar]

Good question. The first option is better I think, as you can store as many phone numbers as you need and the queries will be simpler.

[@trentcox9239]

ive got a common_phone table with a foreign key to a phone 'type' (mobile, freephone, landline, etc) and just store the number as a full e164 formatted string. can't go wrong.

[@DatabaseStar]

That sounds like a good way to do it!

[@DK-fn6xr]

Phone numbers are actually a complicated tree-like data structure, see "prefix code".

[@DatabaseStar]

What do you mean by tree-like data structure?

[@DK-fn6xr]

@@DatabaseStar imagine you took all your phone number strings in your database and concatenated them without any spaces. How will you read off all of the valid phone numbers from this long string uniquely? Assume that the phone numbers are not equal length, for example.some have country codes, some are emergency numbers, etc.) Prefix (rather, prefix-free codes) ensure that this decoding is unique and instantaneous. If you form a tree from all the codes, each number is a leaf node of the tree.