as you can see just to store the above I would need a table really wide where the column headers cover every single possible variation of key.

I'm guessing that mysql tables must specify the number of columns when they are created right?

The solution I am considering is to have a table with just two columnsIDdata and data string

Then I could store the key value pairs concatenated in a string with fields separated by colons and semi colons or commas or something.

When retrieving a record I would instead retrieve the string and parse it. That way if a record went four levels deep on its A group of data and another record only went one level deep but went up to F number of groups it would be cope.

Please tell me if I am going at this right or wrong with regards to mysql

r937
—
2012-08-14T19:00:43Z —
#2

PrimeLens said:

I'm guessing that mysql tables must specify the number of columns when they are created right?

every relational database, not just mysql

key/value pairs are best stored as key/value pairs (i.e. one pair per row) instead of as a single string mishmash that has to be parsed to be searched

but key/value pair schemas are ~notoriously~ difficult to pull meaningful data from -- do a search for EAV (entity-attribute-value)

if at all feasible, you should anticipate all possible keys and declare the table with one column per key, using NULL for the values that do not pertain to a given row

PrimeLens
—
2012-08-14T19:10:56Z —
#3

thanks for answering

r937 said:

if at all feasible, you should anticipate all possible keys and declare the table with one column per key, using NULL for the values that do not pertain to a given row

I would like to do that but the nature of this data is that number of fields per record can vary. There would be roughly 100+ fields per record spread out over a range of approx 1000 (best guess) possible keysNo matter what column width I choose there is always the possibility that a record can come along with one field too many.

What do you think of my solution to have just two fields and parse the string?Are there other solutions that you can think of?

r937
—
2012-08-14T20:29:27Z —
#4

PrimeLens said:

There would be roughly 100+ fields per record spread out over a range of approx 1000 (best guess) possible keys

okay, a predefined table is infeasible

PrimeLens said:

What do you think of my solution to have just two fields and parse the string?

i believe i already commented on it

it won't scale because every search requires a complete table scan

PrimeLens said:

Are there other solutions that you can think of?

EAV

PrimeLens
—
2012-08-14T20:45:33Z —
#5

Thanks !

kduv
—
2012-08-14T23:48:56Z —
#6

You may want to consider a non-relational DB like MongoDB or similar. Many of them are schema-less and can have a variable amount of "columns" per record.

oddz
—
2012-08-15T06:47:06Z —
#7

In regards to relational databases EAV would be the *best option given the circumstance. Depending on what needs to be done with the values it *might be best to break out the value column into multiple columns for different data types. So when the value represents a date it can properly be stored as a date at least, same with integers. Storing everything as a string can lead to big problems down the line. This is especially true when it comes to supporting virtual foreign keys, where a value references another table by an surrogate primary key. This really sucks but it sucks even more when you need to cast strings to integers when making a comparison against two tables in a join. How I would set it up would be entirely dependent on the data types which need to be supported. If it is only integer values you are dealing with than I would probably go the route of the value column being an integer type. However, if the data type could be best stored as either a string, integer, date, etc depending on context I would go the route of multiple value columns with proper data types defined.

wwb_99
—
2012-08-15T11:44:31Z —
#8

EAV is never a good option. Seriously.

r937
—
2012-08-15T13:51:38Z —
#9

wwb_99 said:

EAV is never a good option. Seriously.

so your suggestion for this scenario is then... ?

wwb_99
—
2012-08-15T14:08:22Z —
#10

Hard to understand what the problem really is - IDdata.Col1 isn't exactly descriptive. Would help to know how this thing is getting queried, etc.

That said, a few surface options would be:* Actually fit it into a relational model -- again, no idea what the actual problem here is. But that might be workable.* MongoDb or better yet CouchDb. Let documents be documents.* Doing some sort of hybrid approach with a set of standard metadata columns and then a serialized XML / Json payload column.

Jeff_Mott
—
2012-08-15T14:17:50Z —
#11

PrimeLens said:

I'm a mysql noob ... varying number of key fields per record ... I would need a table really wide where the column headers cover every single possible variation of key.

This sounds like a textbook many-to-one relationship. The way you handle this is by creating two tables. The first is the IDdata table:

IDdata

| id | some_other_data |------------------------

The second table could be called IDdata_attributes, or IDdata_properties, or whatever works for your application's nomenclature.

IDdata_attributes

| IDdata_id | attribute | value |

^ -------- PK --------- ^

The IDdata_id field relates an attribute-value back to a specific IDdata record, which lets you have multiple attribute-values related to the same IDdata.

EDIT: To further illustrate for the OP, here's how your data would look.