Question 162136 in Mysql Question posted in
The official MySQL documentation can be found here.

Can I create a col in MySQL that supports different values?

So i have a table that’s for products and depending on the product the col can have different values, example:

Product DVD:
SKU Name Price Size(MB)

Product Book:
Sku Name Price Weight

Deppending on the product that is "DVD, book or Furniture", i have to change the column

I thought about using 3 different tables but i think it got ugly for legibility purposes

Tags:

2

Answers


  1. 0

    You can create ‘basic’

    create table Products ( Upc varchar(100), Description varchar(15), Price1 money).

    And another table for ‘specific’ features like

    create table ProductFeatures( upc varchar(100), Param varchar(100), Value varchar(100).

    But this design will miss data types…

    Login or Signup to reply.
  2. 0

    You are looking for the data type "sql_variant" BUT don’t go there! sql_variant mucks things up. I don’t remember the specifics but I do remember that down the road I was kicking myself (Quite HARD) for having used it — You have been warned.

    Put them all into a varchar and then treat them differently in code is my suggestion. You can make views that casts the column to a particular data type depending on the product type if you need to.

    Another good approach that works in many cases is to create a child table to the Product table to hold the non-uniform data… ie. Product_Attribute table with AttributeType and AttributeValue colums. The nice thing about the child table is that the attributes for a product that you have to keep track of only grows over time as the system matures. You are ready for this growth with a child table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search