April 25, 2020 at 4:32 pm
I completely agree with Celko.
Heh... being stuck with character-based identifiers due to a legacy system is one thing. Arbitrarily saying that all identifiers should be character-based because they're not used in calculations is another and completely incorrect position to take.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2020 at 7:36 pm
>> Now it is you that is putting words in my mouth. ?? That's not what I said. <<
No I think you misread, this principle is from Brent Ozar. I hope I did not credit you with his work.
>> If people stored data as the way it would ultimately be used, then people would always store dates and times as strings, dollar amounts with $" signs, and all identifiers that used dashes and leading zeros for display with those dashes and leading zeros being stored. <<
No, I do not confuse storage with display. In fact if you look at most of my postings, I have a mania about the separation. If a value needs to have arithmetic computations, then I want to see it stored as INTEGER, FLOAT, REAL or whatever. If it is temporal data, then it needs an internal temporal storage. If it is text, then it needs to be stored as character data (probably with a hell of a lot of restrictions in the form of regular expressions on it).
To use the simple example in this thread, a ZIP Code is 5 digits. I can't do any math on it that makes any kind of sense. How do I prevent a moron from taking the square root of my ZIP Code? It is not an INTEGER! I also see that the zeros in the front of it have meaning,. Why do I want to make displaying it as difficult as possible by putting constraints on an INTEGER column, so that I have to re-pad it with those leading zeros (as a mathematician, please let me tell you that you know that they're not leading zeros because they have no numeric value).
Would you take a column that should be INTEGERs, such as quality on order, and model them with the date? A date is also sequential. Just like INTEGERs. And it is possible to do arithmetic on them. Oh sure, when I try to do an average or something. It gets a little messy but I can kludge this, just like you want to do.
Again, the Ozar principal is good; "store that it the data the way it is used and use it the way it is stored".
Please post DDL and follow ANSI/ISO standards when asking for help.
April 25, 2020 at 8:10 pm
>> Now it is you that is putting words in my mouth. ?? That's not what I said. <<
No I think you misread, this principle is from Brent Ozar. I hope I did not credit you with his work.
>> If people stored data as the way it would ultimately be used, then people would always store dates and times as strings, dollar amounts with $" signs, and all identifiers that used dashes and leading zeros for display with those dashes and leading zeros being stored. <<
No, I do not confuse storage with display. In fact if you look at most of my postings, I have a mania about the separation. If a value needs to have arithmetic computations, then I want to see it stored as INTEGER, FLOAT, REAL or whatever. If it is temporal data, then it needs an internal temporal storage. If it is text, then it needs to be stored as character data (probably with a hell of a lot of restrictions in the form of regular expressions on it).
To use the simple example in this thread, a ZIP Code is 5 digits. I can't do any math on it that makes any kind of sense. How do I prevent a moron from taking the square root of my ZIP Code? It is not an INTEGER! I also see that the zeros in the front of it have meaning,. Why do I want to make displaying it as difficult as possible by putting constraints on an INTEGER column, so that I have to re-pad it with those leading zeros (as a mathematician, please let me tell you that you know that they're not leading zeros because they have no numeric value).
Would you take a column that should be INTEGERs, such as quality on order, and model them with the date? A date is also sequential. Just like INTEGERs. And it is possible to do arithmetic on them. Oh sure, when I try to do an average or something. It gets a little messy but I can kludge this, just like you want to do.
Again, the Ozar principal is good; "store that it the data the way it is used and use it the way it is stored".
It sure is but... go ask Brent if he recommends storing numeric identifiers (Sequence, column with an IDENTITY attribute, or even a date for a Calendar table) as text-based columns simply because you'll never use them for math. 😉 He lives 2500 or so miles from me but I'm sure I'll be able to hear his laugh from here followed by a mighty "It Depends but not usually".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2020 at 5:22 pm
ScottPletcher wrote:You corrupted your data (at least for a relational model). You made it non-atomic, since that column now contains two pieces of info (type and id#), not one (just id#).
Instead you could have assigned new, and non-overlapping, numbers to whichever type of employees there were fewer of.
Our database did not "own" the employee data. We merely received a revised daily extract from HR, and had no authority to redesign any of it.
In our database we used an identity column as the surrogate key for the table with the imported data, with a unique index on the imported employee ID. Not ideal but it worked well enough for over 10 years. I don't think that is too bad considering that it started out as a glorified spreadsheet to track a single project and eventually supported a couple of dozen automated in-house processes.
Really, it remained a spreadsheet. The biggest problem in tables is always the lack of doing any actual data design, including the normalization process, up front, before any physical table is created. Once you've skipped that part, you're destined for a less-than-good data situation from then on.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply