Should I use multiple fields or JSON?

Question

Hello.

I'm designing a DB and would like to know the best way to store this:

I have an user table and let's say it can have 100 item slots, and I store an ID. Should I use JSON ({slot1:123232,slot20:123123123,slot82:23123}) or create 100+ fields (slot1, slot2, slotn)?

Thank you.

Answer 1

With database design normalization, you should not have multivalued attributes.

You might want this.

Users
=====
UserId


UserSlots
=========
UserId
SlotId
Value

Slots
=====
SlotId
Value

Answer 2

Third alternative, create another table for slots, and have a one-to-many relationship between users and slots. Your business logic would enforce the 100 slot limit.

I would recommend against doing the embedded JSON in the database. I'm not sure what DB you are using, but it will likely be very difficult to query the actual slot data for a given user without pulling out and parsing all 100 records.

To create a one-to-many relationship, you'll need a second table

Slots
    id (primary key)
    user_id (mapping to user table)
    item_id (your slot # you want to store)

Now, you can do useful SQL queries like

SELECT * FROM Users,Slots WHERE Slots.user_id = Users.id AND Slots.item_id = 12345

Which would give you a list of all users who have slot item #12345

Answer 3

You should not create 100 fields.

Create a table with two fields, the ID and your "JSON Data", which you could store in a string or other field type depending on size.

You could normalize it as others have suggested, by that would increase your save and retrieve time.