Can someone tell me what the technique is called when you store fields and values split in a database delimited by a comma for example. Ive seen it being used but wasnt sure what it was called.
id | options
1 | option1=this, option2=that, option3=other
2 | option1=this, option2=that, option3=other
3 | option1=this, option2=that
it's called a comma-separated list of attribute-value pairs, and is a perfectly valid thing to do...
...in rare and exceptional circumstances!
I'm with Paul; "Stupid" is the first word that comes to my mind.
BTW, the reason why this is "dumb" or "stupid" or "a really bad idea" is that this is better done as a separate table with a foreign key field pointing back to the id field in the first table, a "key" field, and a "value" field. this allows key/value pairs to be individually indexed, searched, and manipulated (add, delete, update).
and even then, this is only useful in some circumstances - where there are a variable number of unknown/arbitrary keys that a given record might have associated with itself. if the name and number of keys is known in advance, there are better ways to handle it.
wikipedia has several good introductory articles on databases and database normalisation that are worth reading. start with Database Models and Database Normalisation
According to a couple of books on my shelf, the term you're looking for is:
While there are exceptions to every rule, multipart fields are rarely needed.
Multipart fields can wreak havoc with data integrity and should be avoided. Problems most often come up when you try to edit, delete, or sort the date in a multipart field.
When you "normalize" your database and ensure that each field stores only a single value you are doing yourself (and others) a huge favor down the road by helping to ensure that you have data integrity and accurate information.
I call it a violation of the first normal form.
I agree, it's stupid
IF
this is your application's data model.
This is actually quite beneficial if you are simply persisting something that was parsed elsewhere, such as user options in a client app. Makes the client to the work of parsing. This is a trivial storage mechanism that can easily be split and stored in a collection client-side.
Loosely, it's a "repeating value" and violates (at least) First Normal Form (sort of): http://en.wikipedia.org/wiki/Database_normalization
Comma seperated values is close.
I'm not sure why you would repeat field names for every value though.
You can use array types in PostgreSQL to achieve something similar to what you're after, but it's still a Really Bad Idea (tm).
This is how I would prefer to do it if you will be doing anything with this data besides simply persisting it.
id | opt | content
1 | 1 | this
1 | 2 | that
1 | 3 | other
2 | 1 | this
2 | 2 | that
2 | 3 | other
3 | 1 | this
3 | 2 | that
3 | 3 | other
