2

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?

Improve this question
1
  • upvote for store it as a json array in the same row. Commented Jul 5, 2011 at 16:48

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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

Improve this answer

2 Comments

Point is, data in the array does not need to be changed, at all.
ya, but lets say you are given a contact id and want to do some manipulations with it, it'll be easier to do with a second table. But this is purely dependent on your requirements.
1

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.
Improve this answer

Comments

1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.