In MySQL what is the best way to store and retrieve data that could be different types?

Question

I'm not sure if I'm doing this right, or if there is a better way in MYSQL

I want a structure where an object can have an unlimited number of associated data values so that a user can add as much descriptive data as they want to associate with it. I don't know what they could put in ahead of time. (The example below is just simple examples and aren't specific to this issue.)

Object

ObjID	ObjName
67	Dallas
90	Austin

Data

uID	ObjID	DataName	DataType	StringValue	NumberValue	BlobValue
0	67	Best Bird	{String}	Mockingbird	null	null
1	67	Best Store	{String}	IKEA	null	null
2	67	Emblem Icon	{Blob}	null	null	a2fe4f1...
3	90	Avg Salary	{Number}	null	76,563.05	null

My Questions are:

• When put or post data comes in it is formatted as {uID:0, Value:"Bird2"}. Is there an elegant way to write to the correct type column without first querying what DataType it is?
• When selecting all of the data for an Object, I don't want it to include all of the data columns, but just the one that is for its DataType. Is there an elegant way in MYSQL to select that column?
• Is this the best way to approach this problem?

Thanks!

Answer 1

@WunderKlaus as far as figuring out the data type, the easiest way to do that would be in your code before you even do the CRUD operation on your DB. So determine what the type is that is coming in and then based on that store the value in the appropriate column in your table.

As far as getting the right value for the record, you can use the COALESCE() function. To use it you list all of the columns in your table that the value could possibly be stored in and MySQL goes from left to right and returns the value of the first column that isn't null.

Here's an example of how to use it:

SELECT COALESCE(`StringValue`, `NumberValue`, `BlobValue`) AS `value`
FROM ......

Here's the documentation on COALESCE().