September 29, 2011 at 5:34 am
Dear Reader,
I am used to posting on microsoft.public.sqlserver.server.
This forum is new for me.
Please point me in the correct direction for the following.
We use a value table, where the fields are tagged with their 'attribute names'. Reason: On design time the attributes are not known. The value's can be anything, int, float, date, time, datetime general comment etc. All is stored in a varchar value field.
Off course 🙁 we have problems; format, local issues, selecting, sorting, converting, etc.
Specifically for datetime fields. (Selecting, Sorting, Converting, displaying everything runs into problems). (Example select the last two weeks and display them).
BEST PRACTISES. (on this)
That is what I am looking for.
Something on the web I can read and point to, too establish best practises in my firm.
Can anybody give some pointers for this? Or point me in the right direction. (With this question).
Thanks for your time and attention,
Ben Brugman
September 29, 2011 at 7:48 am
Welcome to SSC. All the challenges you are running into is exactly why you want have strongly typed data. As you are discovering things like date ranges are just about impossible to retrieve accurately. On particular challenge with storing dates in varchar fields is what does 1/1/2011 mean? Is that Jan-1 or 1-Jan? That will depend on the way it is input. Another problem is it is very challenging to validate proper dates. 1/32/2011 is perfectly fine in a varchar but bombs when casting to a datetime.
The design you are describing sounds like it will be extremely painful to support in the long run. I am pretty certain that just about everybody around here will tell you that the best practices are to store data in the proper datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 10, 2011 at 7:51 am
Sean Lange (9/29/2011)
Welcome to SSC. snip snip. On particular challenge with storing dates in varchar fields is what does 1/1/2011 mean? Is that Jan-1 or 1-Jan? That will depend on the way it is input. Another problem is it is very challenging to validate proper dates. 1/32/2011 is perfectly fine in a varchar but bombs when casting to a datetime.
Thanks for your time and reply.
The above (quoted) problem is not present in our 'solution'. But plenty problems remain.
Offcourse strong datatyping should be used whenever possible. But as said, at design time we do not know what attributes will be used and what the rules are for these attributes. New attributes are added daily.
So for me one of the solutions would be to to have a distinct field for strings, floats and for datatime attributes and only fill the appropriate field. Or should there be a field for integer, bigint and tinyint as well. And what happens with other constraints.
An option would be to fill the string always with the 'representation' string of a value or date time.
This problem must have been solved hundreds of time in the past. And although I can come up with my own solution I think using the experience of others is very valuable.
So that's why I asked is there a best practises on this.
Thanks for your time and attention,
Ben Brugman
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply