Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Best practice to preserve the big data for table

I have table,

Table Book

id,name,buyers

And each row has the list of buyers.

buyers are about 100000.

So now I have two ideas.

First one is

Push the array of 100000 elements in row

id, name,       buyers
 1, nice story,[1042,13534,14534,15353,,,,,.....]

Second one is

Make another table

Table Buyers

buyer_id, book_id
1042   , 1
13534  , 1
14534  , 1
15353  , 1

I guess total database size is small for Idea 1 but it’s not good way to handle such a big array ?

Which one is faster and best practice?

Tags:

2

Answers


  1. 0

    Storing the data in JSON is actually larger than storing integers.

    A number stored as text takes more bytes than a number stored as a binary integer. For example "255" takes three bytes as text. But the same integer value takes only one byte stored as 0xFF.

    As for performance, how would you add one value to an existing list, or delete one value? This is easy if you store a second table — you just INSERT or DELETE one row. But how would you do it if it were stored in JSON?

    How would you make sure the list has only unique values?

    What if you need to know the count of values in the list?

    If the values must be foreign keys, i.e. must exist in the primary key of another table, how can you enforce that?

    JSON can store a variety of other formats, so how would you ensure that this list remains only a list of integers?

    Most of these are solvable, but my point is the solutions are not as straightforward and the SQL will be more complex. It will take more time for you to learn how to do it, and test. It will take more time for any other developers to join your team and learn how to maintain the code.

    The best practice you asked about is to do the simplest thing that will possibly work. In this case, it means when you use a relational database, store the data in relations. Using JSON is not a relation.

    Login or Signup to reply.
  2. 0

    In my opinion second approach is better.

    Table: Book (id, name) & Buyers (buyer_id, book_id) concept is best approach.

    Because,

    1. your first approach is too complex to do any query on same table.
    2. If you want to update any data related to Book or Buyer then it’s also difficult.
    3. SQL column length is fixed. In future if 100-500 addition buyer will come then it’s again difficult to update the column.

    So, my opinion 2nd approach is best for any scenario.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search