1

Actually i want to save Base Abilities for game. i have two option one is to save a very long string like this [{'baseId':1, 'baseName': 'xyz'},{'//', '//', 'etc'}] or the second option is to save these type of record in multiple rows and column. Or if someone has a better option to do this you can suggest me and thanks in advance. :)

Community
  • 1
  • 1
waleed bin khalid
  • 112
  • 8
  • 1
    answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. – Daryl Gill Nov 21 '16 at 17:37
  • 1
    @DarylGill I disagree. Since he specifically tagged `mysql` then I would argue that the data shouldn't be stored like this. If he wants to store serialized data in that format then he should probably use something like nosql. – bassxzero Nov 21 '16 at 17:39
  • 1
    @bassxzero that maybe true, specifics have not been made clear, op is giving two possible options and asking for 'better'. Maybe a opinion based closure may not be best, IMHO it's either that or being too broad – Daryl Gill Nov 21 '16 at 17:43
  • Perhaps too broad, perhaps likely to attract opinion based answers. It is also probably a duplicate of this question http://stackoverflow.com/q/15367696/1048425 in which I think the highest rated answer summarises perfectly in most circumstances either use a proper NoSQL solution, or use a relational database with one value per row/column – GarethD Nov 21 '16 at 17:46

3 Answers3

1

This is a common problem. The normal answer is: "Use the relational database as it should be used, with well defined columns and rows." That is, your second option.

Storing a complex JSON (or other type) object in a field can be a reasonable thing to do. However, you need to be sure of the following:

  • The field is only being retrieved to pass back to the calling application.
  • The field will never be used for filtering results.
  • Components of the field will never be needed.

(I can add that for JSON specifically, the third can be relaxed in the most recent version of MySQL because of JSON support.)

In other words, the field is a "black-box" or "blob" that has no meaning to the database.

Your values would seem to be relevant for storage in a database. My reaction is that you should parse the values and store them in a more correct relational format.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

This is a typical object oriented to relational mapping problem.

In short it depends on the ease of READs and ease of WRITES.

If you think that this data is always going to be retrieved as a whole JSON and being written a whole you may want to use MySQL JSON Data Type or even a varchar like type.

Otherwise map the fields to a new table and and store as rows. Here you may be better off with one row representing 1 object instead of complicating futher and spanning the field to rows. Again look as your READ and WRITE use cases with respect to partial or full entity.

Then there is a programming model to consider - which model keeps your code simple and less prone to bug.

The other aspect is the effeciency of storage as well. How many records are going to exist ? The data type affect this e.g. blob types TINYTEXT and LONGTEXT are different when it comes to storage.

All these askects need to be considered.

bhantol
  • 9,368
  • 7
  • 44
  • 81
0

There is a 3rd option You store the items in multiple tables.

Firstly your main table for the very basic details that everyone will have. In this case probably the name, and a few other bits.

A second table contains a list of all the abilities that each character may or may not have.

The 3rd table has multiple rows for each character, one for each ability that the particular character has.

This way details may be checked directly using SQL, but if extra abilities are added for certain characters in the future there is no need for any table changes, merely just adding a bit of extra data.

Kickstart
  • 21,403
  • 2
  • 21
  • 33