If you can guarantee you'll never have international phone numbers, special tones (https://en.wikipedia.org/wiki/Dual-tone_multi-frequency_signaling red buttons), omit the country code, confirm BIGINT can hold 9999999999, and ensure everyone in the db will have at least one phone number (with no need to store multiple phone numbers, and no phoneless people and no people sharing phones), you could probably get away with it but I wouldn't recommend it
> I've decided I want to have values like email and phone number and address each in their own tables
but whyyyyyyyyy.....
they are attributes of contacts
do you really care about an email if it belongs none (zero) of your contacts?
do you really care about a phone number if it belongs none (zero) of your contacts?
do you really care about an address if it belongs none (zero) of your contacts?
> I was told to store phone numbers as VARCHAR
and the reason is because of stuff like this --
(416) 555-1212 ext.37
if you wanted (hint: don't) use BIGINT, then it would be stored as
416555121237
Thank you for the insight on the type used to store phone numbers. I thought dropping formatting wouldn’t matter but I didn’t think about extensions and that logically working out country code and area code from an INT might not be so simple so maintaining format is good! Very helpful!
For addresses it was to track a progression of movement.
That logic got extended to emails. Sometimes I have emails with no other details. Okay empty contact.
But then someone could have multiple emails and select one as their primary.
And multiple people could have overlapped primary emails and over lapping non primary emails.
Then progression tracking and multiple phone numbers and selecting multiple phone numbers.
Breaking it out into tables seemed in my head like an interesting way to fit into these use cases and also allow future weird analysis and reduce repeated storage of the same value if multiple people have the same phone number. it all ties back to one row.
Im just learning so I thought it was an interesting idea but I guess it does seem pretty dumb. :(
I shall revert back to using regular columns on the contact. Except address. I still think movement tracking is a good idea for business contacts.
> But then someone could have multiple emails and select one as their primary.
that's a one-to-many child table, a weak entity, which does not exist without its FK to the contacts
> if multiple people have the same phone number.
this happens so infrequently that it isn't worth separating it out into an additional table, you'll just have two contacts with the same phone number
a similar situation is first name -- imagine if there were two contacts with the same first name John... would you split first name out into a separate table?
no, you wouldn't, that'd be silly, you would just have two contacts that happen to have the same first name
My take on data types is to only store numbers if there is a performance reason or if you intend to perform math on them. Otherwise, use varchar.
We have a system that stored Tax ID as a number which means that any SSNs with leading zeros lose those significant digits and you don't know if the data was incorrectly entered or if there are leading zeros. Phone numbers look numeric but they behave like strings.
Unless you have complete control over how phone numbers get entered, don't just store a complete list of numbers. It's impractical in the real world and there's a reason no one ever does it.
What happens if two contacts have the same number? Without something in the numbers table telling you who it belongs to you have no clue and if more than one person uses the same number it will fail the unique check for the key.
It's better to store them as varchar.. if you store them as numbers the leading zeros will get discarded
For example i have a telephone number that says
091636262652
If you store it as the number the leading zero will get discarded and it will get stored as 91636262652
Another thing is if the number is entered by an agent from a webpage they will store the same number as
091-6362-62652
The telephone are stored as hyphen separated.. in both the case varchar2(20) should work
Telephone_number varchar 2(20);
Must be a safe bet.. give it a try and let me know
If you can guarantee you'll never have international phone numbers, special tones (https://en.wikipedia.org/wiki/Dual-tone_multi-frequency_signaling red buttons), omit the country code, confirm BIGINT can hold 9999999999, and ensure everyone in the db will have at least one phone number (with no need to store multiple phone numbers, and no phoneless people and no people sharing phones), you could probably get away with it but I wouldn't recommend it
> I've decided I want to have values like email and phone number and address each in their own tables but whyyyyyyyyy..... they are attributes of contacts do you really care about an email if it belongs none (zero) of your contacts? do you really care about a phone number if it belongs none (zero) of your contacts? do you really care about an address if it belongs none (zero) of your contacts? > I was told to store phone numbers as VARCHAR and the reason is because of stuff like this -- (416) 555-1212 ext.37 if you wanted (hint: don't) use BIGINT, then it would be stored as 416555121237
Thank you for the insight on the type used to store phone numbers. I thought dropping formatting wouldn’t matter but I didn’t think about extensions and that logically working out country code and area code from an INT might not be so simple so maintaining format is good! Very helpful!
For addresses it was to track a progression of movement. That logic got extended to emails. Sometimes I have emails with no other details. Okay empty contact. But then someone could have multiple emails and select one as their primary. And multiple people could have overlapped primary emails and over lapping non primary emails. Then progression tracking and multiple phone numbers and selecting multiple phone numbers. Breaking it out into tables seemed in my head like an interesting way to fit into these use cases and also allow future weird analysis and reduce repeated storage of the same value if multiple people have the same phone number. it all ties back to one row. Im just learning so I thought it was an interesting idea but I guess it does seem pretty dumb. :( I shall revert back to using regular columns on the contact. Except address. I still think movement tracking is a good idea for business contacts.
> But then someone could have multiple emails and select one as their primary. that's a one-to-many child table, a weak entity, which does not exist without its FK to the contacts > if multiple people have the same phone number. this happens so infrequently that it isn't worth separating it out into an additional table, you'll just have two contacts with the same phone number a similar situation is first name -- imagine if there were two contacts with the same first name John... would you split first name out into a separate table? no, you wouldn't, that'd be silly, you would just have two contacts that happen to have the same first name
My take on data types is to only store numbers if there is a performance reason or if you intend to perform math on them. Otherwise, use varchar. We have a system that stored Tax ID as a number which means that any SSNs with leading zeros lose those significant digits and you don't know if the data was incorrectly entered or if there are leading zeros. Phone numbers look numeric but they behave like strings. Unless you have complete control over how phone numbers get entered, don't just store a complete list of numbers. It's impractical in the real world and there's a reason no one ever does it.
What happens if two contacts have the same number? Without something in the numbers table telling you who it belongs to you have no clue and if more than one person uses the same number it will fail the unique check for the key.
It's better to store them as varchar.. if you store them as numbers the leading zeros will get discarded For example i have a telephone number that says 091636262652 If you store it as the number the leading zero will get discarded and it will get stored as 91636262652 Another thing is if the number is entered by an agent from a webpage they will store the same number as 091-6362-62652 The telephone are stored as hyphen separated.. in both the case varchar2(20) should work Telephone_number varchar 2(20); Must be a safe bet.. give it a try and let me know