Store ID's in each row or use Json?

Databases
1

Which is better? store the foreign id’s in each row or foreign id’s in a field using json data.

Example 1:

item_id = a item
option_id = number of option/extra with a item

(primary key, item_id, option_id)
1, 44, 123
2, 44, 453
3, 44, 535
4, 44, 627
5, 44, 447
6, 44, 857

Example 2:
(primary key, item_id, option_id)
1, 44, {“optionid”:[123,453,535,627,447,857]}

I think Example 2 is best solution to reduce the numbers of rows but the tricky part is editing/deleting option_id which has to be done by PHP.

2

Why would you even consider ‘option 2’? Makes no sense and as you said a nightmare to update / edit.

Now if you were using a database that supports array data types it might make sense but definitely not the way you mention here.

3

I am assuming you have an items table and an options table with a many to many relationship between the two. Example 1 is the way you normally join the 2 tables. I would go with that.

hth.

4

And expanding on shastah’s assumption, you don’t need a separate id as a primary key for the linking table, just make the item_id and option_id a joint primary key.

5

Well I guess Example 1 is best option.

I will do something like this, what do you think?

A Item have a few group which linked with number of options. Customer can select a group.

Item Table:

  • Item_ID (Primary Key)
  • Item_Name
  • Item_Description

5, ‘Item Name’, ‘Stylish’

Options_Group Table:

  • OGroup_ID (Primary Key)
  • Item_ID (Foreign Key)
  • Name

7, 5, ‘Group 1’
8, 5, ‘Group 1’

Options Table:

  • Option_ID (Primary Key)
  • OGroup_ID (Foreign Key)
  • Name

4, ‘7’, ‘Option 1’
5, ‘7’, ‘Option 2’,
6, ‘7’, ‘Option 3’,
7, ‘8’, ‘Stuff 1’,
8, ‘8’, ‘Stuff 2’,

6

I have used this pattern a lot and it really, really depends depends on how/what is updating happening. If all reads and writes of the “payload” column are coming from your app and it handles serialization/deserialization it can be a very, very powerful tool to use some of the good parts of relational databases (indexed lookups, referential integrety) and some of the good parts of object database – as in your objects need not be expressed in tables.