SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Store ID's in each row or use Json ?

    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.
    [Home Sweet Home]

  2. #2
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PowerStrike View Post
    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.
    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. #3
    Non-Member
    Join Date
    Jan 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Posts
    245
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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',
    [Home Sweet Home]

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,624
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dc dalton View Post
    Why would you even consider 'option 2'? Makes no sense and as you said a nightmare to update / edit.
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •