2

I have multiple contacts for a user and I want to store them in a single column.

uID |  Contact
----| ---------
2   | 1221
    | 1223
----|----------
3   | 1123

How can I do that?

  • 3
    MySQL doesn't support arrays. But it's better to properly normalize that anyway – a_horse_with_no_name Nov 29 '17 at 8:18
  • since 5.7 or so mysql supports arrays, check the JSON functions. But it can get a bit ugly to use. – John Mar 9 '19 at 5:12
4

You can create separate tables, like user table and contact table:

  • user table

    uID
    ---
    2
    
  • contact table

    Contact | uID
    1221    | 2
    1223    | 2
    
| improve this answer | |
3

As suggested above, the best way is to have two tables - one for user and the other for contact.

From your example it appears as if you have nothing more than an id for the user. If that is the case, and if you know for a fact the maximum number of contacts a user has (let’s say 3), then you can use a single table with (userId, contact1, contact2, contact3) as the columns and allowing nulls for the last two columns. Problem with this: not flexible when you want to add more contacts for a user or if you want to add more info about the user.

Another alternative (if you don’t want to have multiple columns) would be to use a single column for contact, but add them as a single string with separators. For example,

  • uID | Contact
  • 2 | 1221,1223
  • 3 | 1123

Problem with this: querying based on contact becomes a pain.

Out of these 3 options, I would recommend the first solution (having two separate tables) as suggested by someone else in the answers above. If you don’t want that, third option would be the next best.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.