Re: design question

"Chris Seidel" <cseidel_at_arcor.de> wrote in message news:48c145ea$0$11093$9b4e6d93_at_newsspool1.arcor-online.net...
> DA Morgan wrote:
>> "Best" depends on what you are going to do with it other than store
>> it.
>
> Query it. I have to find objects fast based on the field data (=, >, <,
> between, like)
>
>
>> "Best" depends on whether you require it reconstituted as XML at some
>> point in the future.
>
> No XML needed.
>
>> "Best" depends, to a lesser extent, on your version which you don't
>> state (3 decimal places).
>
> A stores all as string, no problem.
> For B a new column will be there numeric(x,y) with y >= 3.
> But for 99 % of the objects 2 decimal places will be ok.
>
>> My preference for reasons that range from performance to reporting to
>> storage to relational theory is to shred XML into its constituent
>> elements storing each with its proper data type.
>
> So you would prefer A?
> What about the many null columns in this approach?
>

Store numbers as numbers, dates as dates, strings as strings etc. Not doing so will cause you a lot of grief later. Also the optimizer can do things for proper storage that improper storage won't allow it to do. For example, with dates the optimizer knows how many possible entries are between two dates if the dates are represented as dates. It cannot tell if the dates are represented as strings (since there are a huge number of entries between two string values).Same with integers and if you have a constraint on the field the optimizer can take that into account. If you want to do comparisons between date fields you are going to have to compare strings converted to dates which can cause a full table scan. For example show me the results where x rows are within three days of each other. (also you have the potential of a date stored as a string being an improper date same with number, eg November 31st, 2008. -0. etc. ) Your model A looks a lot like the one table EVA model which is sub-optimal. (sucks).

Relational theory has been around for a long time. (over 25 years) It has been highly effective for large and small businesses. It isa well known pattern. Use what other people have learned to your advantage. Use the correct tool for the correct job. Making the db a string bit bucket isn't going to help your efforts.

Jim Received on Fri Sep 05 2008 - 21:38:52 CDT