Storing multipe datatypes in single sql server table column..

User 4122428

Hi All, I'm looking to be able to predefine a particular user input as String, Int, or Dec (maybe others), as the particular case may be. The available datatypes are stored in a table, associated with the property (see below) that they apply to. When the user submits the filled in textbox, that entry will be put in a table column, probably as a varchar, and converted as needed, to or from the appropriate datatypes as predefined. For example, the textbox may be asking for the property of "Length," for which "Dec" is the predefined datatype. Or, the textbox may be asking for the property "Count," for which INT would be the datatype, or "Color," for which varchar would be the datatype. A single column will store all of the results, no matter which kind of datatype is actually involved. The Question: Is there a standard way of doing this kind of thing? Can you throw some keywords or techniques at me? Is it simply a bad idea to do what I'm talking about doing?

Not Active

Member 4125480 wrote:

Is it simply a bad idea to do what I'm talking about doing?

Yes. You could store the values as string (VARCHAR) and convert as necessary, but this is frought with dangers and misuses. Another option may be use a UDT, user defined type. Or possible use an xml column and store value and data type information.

I know the language. I've read a book. - _Madmatt

Luc Pattyn

Member 4125480 wrote:

Can you throw some keywords or techniques at me? Is it simply a bad idea to do what I'm talking about doing?

I can throw some mud at you. It is a horrible idea. Come to think of it, are you by any chance the Lotus Notes architect? :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
[The QA section does it automatically now, I hope we soon get it on regular forums as well]

modified on Monday, February 8, 2010 7:57 AM

Lost User

Member 4125480 wrote:

The Question: Is there a standard way of doing this kind of thing? Can you throw some keywords or techniques at me? Is it simply a bad idea to do what I'm talking about doing?

In effect, you'd be storing everything in a list, without type-safety. Using a database would be kinda overkill where a textfile would suffice. A bit of a mean remark, but a database is best suited for relational data :)

Member 4125480 wrote:

or "Color," for which varchar would be the datatype.

I'd expect a color to be a number. You'd best prepare for a lot of conversion-problems, mostly centered around datetime and decimals.

I are Troll :suss:

User 4122428

I looked up UDTs today, and one thing that has come up is that they aren't supported in Azure, which is somewhat part of the long term plan.