tags:

views:

65

answers:

3

I have a situation where I need to store a general piece of data (could be an int, float, or string) in my database, but I don't know ahead of time which it will be. I need a table (or less preferably tables) to store this unknown typed data.

What I think I am going to do is have a column for each data type, only use one for each record and leave the others NULL. This requires some logic above the database, but this is not too much of a problem because I will be representing these records in models anyway.

Basically, is there a best practice way to do something like this? I have not come up with anything that is less of a hack than this, but it seems like this is a somewhat common problem. Thanks in advanced.

EDIT: Also, is this considered 3NF?

A: 

If I were to do this I would choose either your method, or I would cast everything to string and use only one column. Of course there would be another column with the type (which would probably be useful for the first method too).

For faster code I would probably go with your method.

rslite
yeah, I forgot to mention, I initially used all strings and `Integer.parseInt()`, but that proved to be far too slow (and resource intensive -- a fairly simple app should not hog CPU).
twolfe18
+1  A: 

If your application can reliably convert datatypes, you might consider a single column solution based on a variable-length binary column, with a second column to track original data type. (I did a very small routine based on this once before, and it worked well enough.) Testing would show if conversion is more efficiently handled on the application or database side.

Philip Kelley
+1  A: 

You could easily do that if you used SQLite as a database backend :

Any column in a version 3 database, except an INTEGER PRIMARY KEY column, may be used to store any type of value.

For other RDBMS systems, I would go with Philip's solution.

Note that in my line of software (business applications), I cannot think of any situation where this kind of requirement would be needed (a value with an unknown datatype). Unless the domain model was flawed, of course... I can imagine that other lines of software may incur different practices, but I suggest that you consider rethinking your overall design.

Mac