I need to store key value pairs for an article in a database. Articles
have unique ids. There are varying number of fields of different
types. I've tried giving each field a column but some fields can
appear more than once, so right now I have a mess of tables.

I was thinking it would be nice to have one table with the columns:
id, field, value.

I can do that with one type at a time, like TEXT or INT or
VARCHAR(255) but is it possible to put different types in the same
column? I want to be able to SELECT * WHERE value = 2 and have that
also search for "2" in TEXT/VARCHAR fields.

Can I use a BLOB with type casting? is that a good idea?

techcalgary@hotmail.com wrote:
> I need to store key value pairs for an article in a database. Articles
> have unique ids. There are varying number of fields of different
> types. I've tried giving each field a column but some fields can
> appear more than once, so right now I have a mess of tables.
> 
> I was thinking it would be nice to have one table with the columns:
> id, field, value.
> 
> I can do that with one type at a time, like TEXT or INT or
> VARCHAR(255) but is it possible to put different types in the same
> column? I want to be able to SELECT * WHERE value = 2 and have that
> also search for "2" in TEXT/VARCHAR fields.
> 
> Can I use a BLOB with type casting? is that a good idea?
> 

i don't think you can a field of more than one data type. that is why we 
  have varchar(xxx). varchar gives you the capability of storing 
whatever you want. to help you out with your situation, my idea is that 
try delimiting values with a defined (for yourself) delimiter such as | 
or ~ or something like that. in this way, once you do a select and 
search for the delimiter within that string, you know exactly how many 
fields you have. further you don't have to worry about data type. with 
the help of conversion function (in your application language) you can 
find out which ones are integers or character strings.
hope this helps.

On 27 Apr, 14:41, lark <ham...@sbcglobal.net> wrote:
> techcalg...@hotmail.com wrote:
> > I need to store key value pairs for an article in a database. Articles
> > have unique ids. There are varying number of fields of different
> > types. I've tried giving each field a column but some fields can
> > appear more than once, so right now I have a mess of tables.
>
> > I was thinking it would be nice to have one table with the columns:
> > id, field, value.
>
> > I can do that with one type at a time, like TEXT or INT or
> > VARCHAR(255) but is it possible to put different types in the same
> > column? I want to be able to SELECT * WHERE value = 2 and have that
> > also search for "2" in TEXT/VARCHAR fields.
>
> > Can I use a BLOB with type casting? is that a good idea?
>
> i don't think you can a field of more than one data type. that is why we
>   have varchar(xxx). varchar gives you the capability of storing
> whatever you want. to help you out with your situation, my idea is that
> try delimiting values with a defined (for yourself) delimiter such as |
> or ~ or something like that. in this way, once you do a select and
> search for the delimiter within that string, you know exactly how many
> fields you have. further you don't have to worry about data type. with
> the help of conversion function (in your application language) you can
> find out which ones are integers or character strings.
> hope this helps.- Hide quoted text -
>
> - Show quoted text -

I don't understand? Why would you need to delimit the values when
there is only one field/value per row?