CODEWITHSUNDEEP
phpmysql
David Burford
David Burford

Reputation: 791

storing a variable amount of data in a database

I've created (and basically finished) a shopping cart and store front using php, mysql and paypal. There is one thing that is bothering me. I've repeatedly read that its simply bad form to store tokenised data in a mysql column, but I don't know how else to handle this situation.

I allow the admin to add new products and then set optional user choice options for a product. For example they may add a product called "phone" and then add an option called "colour" with accepted choices being "black, white, red, grey". They may also sell A4 paper with no configuration options and a pair of shoes with ALOT of config options (shoe size could have choices ranging from 4 to 13 in ladies and mens sizes for example).

Because I don't know how many options a product will have and I don't want to enforce a hard limit I currently don't have columns for "option1", "option2" etc, instead I just have "option_data" which looks something like "colour|red,black,white,green#memory size|8gb,16gb,32gb" and I explode on # to get the different options and on | to get the option name and possible data and finally split the data on , to get the seperate options. my shop then parses all this and generates drop down tables for the user to select from etc.

So given that storing data like that is seriously frowned upon and that I don't know how many options a product will have so I can't just make a column for each one.. how am I SUPPOSED to handle this?

Thanks :)

Upvotes: 0

Views: 402

Answers (3)

Denis
Denis

Reputation: 5271

Ideally you should normalize your data by creating separate tables.

Table product_options:

id INT
product_id INT
option_name VARCHAR

Table product_option_values:

id INT
option_id INT
value VARCHAR

Upvotes: 1

alandarev
alandarev

Reputation: 8635

I would suggest stick to "a row per variable/value combination". Consider the next table scheme for your products variables:

TABLE Product_variables:
  INT product_id
  STRING variable_name
  STRING variable_value

Then you could have:

product_id, variable_name, variable_value
1, memory_size, 8gb
1, memory_size, 16gb
1, memory_size, 32gb
1, colour, red

Upvotes: 0

Jim
Jim

Reputation: 22656

Best practice would be to add extra tables. One to store different options:

option
    id
    name       -- I.e. colour, memory, e.t.c.
    product_id -- FK link to product

And a table to store the options themselves:

option_value
    id
    name      -- Choice i.e. red,blue, 5GB e.t.c.
    option_id -- FK Link to option

Upvotes: 1

Related Questions