Phone and email in one column, is there any disadvantage?

Question

This is my Contact table

Id, PhoneOrEmail, DataType
---------------------------
10, 123456789   , 1(phone)
11, foo@bar.com , 2(email)

To avoid table explosion (too many tables), I'd like to store some information like phone and email in one column, is there any disadvantage?

any lots and lots so see https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Jul 09 '23 at 12:06
@nbk, no, no, no. It's not (comma) delimited. It's one row for one entry, either phone or email, decided by `DataType`. — Zhang, Jul 09 '23 at 12:10
Why not just put everything in a single column and have the type indicate 100 possible different types of data.... you could have another type column to indicate what data type the other type column really is.... or maybe just don't do this and properly normalise your data! — Stu, Jul 09 '23 at 12:19
@marc_s, not 2 columns, it's 2 tables, if split them. for example, one persone has 3 phones and one email, one-table-two-column wouldn't work -- the email store to which phone? — Zhang, Jul 09 '23 at 12:20
See the lunk thus way I is better ro store than jsin xml or comma they are all the same diffucult to zanle qnd costung much needed resources, so nonono — nbk, Jul 09 '23 at 12:37
Why do you think there is a problem if you have a lot of tables? — Charlieface, Jul 09 '23 at 13:24
Feels like you are going for an EAV structure (Entity Attribute Value). Some folks think EAV is evil. I do not. Under the right circumstances it is the prudent choice. — John Cappelletti, Jul 09 '23 at 16:10

score 3 · Accepted Answer · answered Jul 09 '23 at 12:57

There are, of course, advantages and disadvantages for each option - however unless you have a very (very) limited storage, I really see no reason why you should choose to be so stingy about it and avoid setting up proper normalized tables, especially If you expect only to use either email or phone number.
In such a case, the best design would probably be to simply add a table for phone numbers per person and a table for emails per person.

The one "big" advantage of storing all communications options in one column would be to avoid having your database structure change when adding new ways for communication with a person - but that would require a better column name - something like this:

CREATE TABLE CommunicationMethods (
    Id int PK,
    Name varchar(100),
    CONSTRAINT UX_CommunicationMethods_Name UNIQUE (Name)

)

CREATE TRABLE CommunicationOptions (
    PersonId int,
    CommunicationMethodsId int,
    CommunicationIdentifier varchar(100),
    CONSTRAINT PK_CommunicationOptions PRIMARY KEY(PersonId, CommunicationMethodsId, CommunicationIdentifier)
)

The CommunicationMethods table will hold records like

1, Phone
2, Email

and possibly also

3, Skype
4, Teams
5, Zoom

etc'

and the CommunicationOptions will hold the combination of the person's Id, the communicationMethod's Id and the identifier for that person with this method - so phone number, email address, Skype/Teams/Zoom account etc'
This design will force you to write a more cumbersome and sophisticated code than the simpler design of a table per communication method, but it does have a big advantage when adding new ways of communications with a person - because you're only going to add new data, and perhaps some new validation rules, but the structure will remain the same.

Phone and email in one column, is there any disadvantage?

1 Answers1