Data Of Unknown Type In Column

Databases
1

I am using MySQL and want to be sure of the best way of doing this before moving forward. I want a properties table to describe aspects of products such as size, price, color, and so forth. This is pretty much the idea:

http://stackoverflow.com/questions/2050431/database-design-for-multiple-product-types

But I do not want two separate tables, one for Measurement and one for Trait. I would like to have one table with the property values if possible.

Is it acceptable to store values that are text or numbers in the same column? Can I store a price like 16.99 in a column of data type varchar or text? I read that MySQL will convert strings to numbers and vice versa as necessary. So if a price is stored in a text field, can it be sorted? Can calculations be performed on it or would I have to do the type conversion?

properties_table
id | property | description
---------------------------------------------------
1  |  color     | The color of the item
2  |  price     | Per unit price in USD
3  |  size       | Product size 
4  | weight    | Per unit weight in pounds


product_properties_table
id | product_id | property_id | value
-----------------------------------------------
1  |     1            |      1            | red
2  |     1            |      2            | 39.99 
3  |     1            |      3            | 11
4  |     2            |      2            | 2.79
5  |     2            |      4            | 2.8


products_table
id | category_id     | product_name   | product_description
---------------------------------------------------------------------------
1  |  5  | Nike                    | Athletic shoe 
2  | 16 | Something else  | Something else

This is a simplified example. A product may have multiple properties and there will be multiple types of products. Listing the product properties by column really is not possible in this case.

Am I on the right track? The tables would be joined to get the information I need. To get a product’s price, I would query the product_properties_table with the appropriate product_id and select property_id of 2. See the link above for a better general explanation. Can I store text and numbers in product_properties_table.value? Is there a better way of doing this?

Multiple types of products (or services) and multiple properties is what I need. Any advise will be appreciated. :slight_smile:

2

The simplest way to do that would be to expand “value” into multiple columns. So the “value” would be spread across X number of columns dependent on the types of data supported. Though this is very sloppy. The other way would be to have a separate table per data type or property.

1.)

product_properties

  • product_id
  • property_id
  • val_int
  • val_varchar
  • val_longtext
  • val_decimal
  • val_…

You probably get the idea… (this is similar to how magento does it)

2.)

property_int

  • product_id
  • property_id
  • value

property_text

  • product_id
  • property_id
  • value

property_dec

  • product_id
  • property_id
  • value

And so and so forth for each supported data type.

3.)

field_property_price

  • product_id
  • value

field_property_size

  • product_id
  • value

field_property_color

  • product_id
  • value

So on and so forth for each property type (this is actually how Drupal does it).

Each of these has disadvantages and advantages. However, whenever you’re trying to create a meta-relational archiecture using a relational database you’re going to run into various problems any way you choose to go. It really comes to to picking your poison because all these are going to have major issues down the line from scalability to complexity to performance dependent on what meaningful data you will need to pull.

3

From the way you have described things this really ought to be separate tables.

4

what he said

EAV (entity-attribute-value) is a wonderful, generic scheme for storing data, and a positively, excruciatingly difficult scheme to get meaningful data out of in any efficient or simple manner

you said “Listing the product properties by column really is not possible in this case.”

you need to look as hard as possible at that option – a few hours spent now, avoiding an EAV scheme, could pay off in days and days of agony avoided later

5

@oddz: Thanks for the mention of Magento. I will download the free community version of Magento and take a look at it. I have not looked at Drupal for years. I will look at that, too. Putting data in a different column based on data type seems a bit of a hassle. But since you are grabbing the full row, you could use whichever field has data in it as there should be only one that does.

I was also thinking about another column next to value that has the type of data the value represents. string, int, float, etc. My concern is doing type conversions for purposes of sorting and calculations. If MySQL can handle type conversions on-the-fly as PHP does (or doing the conversion in the SQL), then that concern would be alleviated. Looking at the MySQL documentation, it looks like it can do on-the-fly type conversion.

http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

I will also have to be concerned about prices in foreign currency as well. I did not think about that last night when I was writing the post. Maybe a separate table for prices would be in order instead of stuffing prices into the properties table.

Separate tables for each product type is one option I have seen discussed. But managing dozens or hundreds of tables would probably result in me going insane. As of now, there is not any way to determine which types of products will be input into the database or what properties will be required. Separate tables would require constant tweaking of code, creating tables and columns, and so on. There is no flexibility at all in that method. The goal is to create it and be done with it, not have to go back and add a table and tweak SQL every time a new product type is added.

r937 mentions entity-attribute-value, this is from the Wikipedia article about the topic:

Therefore larger systems use separate EAV tables for each data type (including binary large objects, “BLOBS”), with the metadata for a given attribute identifying the EAV table in which its data will be stored.

A separate table for each data type is a possibility I had not considered. I did read about separate tables for different product types, but not for data types.

When dealing with diverse products (clothing, golf clubs, lawn mowers, electronics, jewelry, services, or whatever), it is not possible to think of every possible property type and fitting it into a column.

But as you say, a few hours spent now can prevent much agony in the future. I need to balance that with flexibility as well. Being able to store as many different types of information as possible without having to go back and create tables or tweak SQL every time a new product type is stored in the DB is my overriding goal now. Set it and forget it. :slight_smile:

Thanks for the mention of entity-attribute-value. I will read up on it.

6

another suggestion to get you going: consider all the attributes that all products have in common

name, description, type, price, availability date, discounts, size (optional), colour (optional), …

i’m sure there are many that are common enough that having a column for them is worthwhile even if optional

what does that leave? oddball attributes, right?

do these oddball attributes have to be searchable? if so, what kind of search? would keyword search work? fulltext search?

consider all oddball attributes in a large TEXT column as serialized keyword:value pairs

structurally a lot simpler than EAV, and possibly (probably) easier to search

7

Having a table with columns for common attributes seems like a good idea. Thanks for the suggestion.

Does anybody have experience with NoSQL data storage systems? I do not have time to delve into it now. But if anyone has experience with any of them (such as [URL=“http://en.wikipedia.org/wiki/Mongodb”]MongoDB) and can share some tips, it will be helpful for future reference.

Since then, MongoDB has been adopted as backend software by a number of major websites and services, including Craigslist, eBay, Foursquare, SourceForge, Viacom, and the New York Times, among others. MongoDB is the most popular NoSQL database system.

One thing I am learning is that MySQL is behind the times. While other database systems have supported recursive queries for years, MySQL does not (commercial DBs have since 2003 or so and Postgre since 2009 I believe). Same thing with table inheritance (never used it, sounds interesting though). What bothers me most about MySQL I think is how InnoDB databases never shrink in size when you delete rows. If you want to reduce the size you have to export your data and import. Why would you design a database system like that?

8