CODEWITHSUNDEEP
mysqlsqljson
Sander
Sander

Reputation: 1401

Storing array with values in database

I have the following data which I want to save in my DB (this is used for sending text messages via a 3rd party API)

text_id, text_message, text_time, (array)text_contacts

text_contacts contains a normal array with all the contact_id's

How should I properly store the data in a MySQL database? I was thinking myself either on 2 ways:

  1. Make the array with contact_id's in a json_encoded (no need for serializing since it's not multi-dimensional) string, and store it in a text field in the DB
  2. Make a second table with the text_id and all contact_id's on a new row..

note: The data stored in the text_contacts array does not need to be changed at any time.

note2: The data is used as individual contact_id to get the phone number from the contact, and check whether the text message has actually been sent.. (with a combination of text_id, and phonenumber)

What is more efficiënt, and why?

Upvotes: 2

Views: 1364

Answers (4)

therin
therin

Reputation: 1468

Almost all implementations would use one table for storing each text_contacts, and then a second table would use a foreign key to reference the text_contacts table. So, if say you had a table text_contacts that looked like this:

contact_id  |  name
    1       | someone
    2       | someone_else

And a text message table that looked like this:

text_id  | text_message  |  text_time | text_contact
   1     |   "Hey"       |   12:48    |      1
   2     |   "Hey"       |   12:48    |      2

Each contact that has been sent a message would have a new entry in the text message table, with the last column referencing the contact_id field of the text_contacts table. This way makes it much easier to retrieve messages by contact, because you can say "select * from text_messages where text_contact = 1" instead of searching through each of the arrays on the single table to find the messages sent by a specific user.

Upvotes: 1

cwallenpoole
cwallenpoole

Reputation: 81988

This topic will bring in quite a few opinions, but my belief: second table, by all means.

  1. If you ever have a case where you actually need to search by that data, it will not require you to parse it before using it.
  2. It is a heck of a lot easier to debug (for the same reason)
  3. json_encode and json_decode (or equivalent) take far more time than a join does.
  4. Lazy loading is easier, even if not necessary in most cases.
  5. Others will find it more readable and, with a good schema definition, easier to conceptualize and maintain.

Upvotes: 1

Paul Sonier
Paul Sonier

Reputation: 39480

This is completely dependent upon your expected usage characteristics. If you will have a near-term need to query based upon the contact_ids, then store them independently as in your second solution. If you're storing them for archival purposes, and don't expect them to be used dynamically, you're as well off saving the time and storing them in a JSON string. It's all about the usage.

Upvotes: 3

Balanivash
Balanivash

Reputation: 6867

IMO, go with the second table, mapping text-ids to contact-ids. Will be easier to manipulate than storing all the contacts in one field

Upvotes: 1

Related Questions