Data Type of a field

Question

Hey guys and gals, I have a field in a table that I will be storing different kinds of data in it, Like: X-Large, Medium, Small....or I might store: 22-March-2009, 1 Year, 2 Years, 3 Years...or 06Months, 12 Months, 1 Year, or I might store: "33-36", "37-40"...and that data is not fixed, i might need in the future to add new categories...

The obvious choice of a data type would be an nvarchar(length), but any other suggestions? is there a way to go around this??? thanks in a advance...

Answer 1

nvarchar(max) would work. Otherwise, you might have multiple columns, one of each possible type. That would keep you from converting things like double-precision numbers into strings and back.

Answer 2

• nvarchar(max) if the data is restricted to strings of less than 2Gb.
• ntext if you need to allow for strings of more than 2Gb.
• binary or image if you need to store binary data.

Answer 3

No matter what you do, such database design does not look good.

Still, you can use BLOB data type to just store any data in a column, or a Text type if it’s text (this way search will work better, understanding upper and lower case and such).

Answer 4

Sounds like you're trying to store a "size". Maybe you need a "Size" table with those values in it (X-Large, Medium, Small, 1 Year, etc.) and an ID field that goes in the other table.

Why you would also want to store a date in the same field is a bit confusing to me. Are you sure you shouldn't have two different fields there?

ETA: Based on your comment, I would suggest creating a couple additional tables:

SizeType - Would define the type of "size" you were working with (e.g. childrens clothing, childrens shoes, mens shoes, womens shoes, mens shirts, mens pants, womens shirts, womens pants, etc.). Would have two columns - an ID and a Description.

Size - Would define the individual sizes (e.g. "Size 5", XL, 33-34, 0-6 Months, etc.). Would have three columns - and ID, a Description, and the corresponding SizeType id from SizeType.

Now on your product table, you would put the ID from the size table. This gives you some flexibility in terms of adding new sizes, figuring out which sizes go with which type of products, etc. You could break it down further as well to make the design even better, but I don't want to overcomplicate things here.