Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fluid
Starting Member
2 Posts |
 Posted - 2003-03-03 : 11:26:03
|
| ok here we go. i really need some backing voices here, or if im wrong, i need someone to smack me in the face and tell me to do a reality check..the conversation i am pasting in the bottom of this message is an ongoing one about how our data should be kept...basically im being told i am flat out wrong on what i think. we have employee id numbers, contract numbers, project numbers, etc...these are all identifying numbers about things in our database. i am being told that if you are not going to do computation with a bit of data, that it is not a number, and should be kept as a character field. ill let you be the judge of it all base on the emails below...they are in descending order...so read from the bottom up, and please reply and be frank!ok...here we go. for one thing, you are adding TEXT to a NUMERIC field to denote whether or not it is a temporary record. thats bad for more than one reason. number one you break the built in methods the database has for forcing data integrity in your data at the front end. you can no longer use auto-number features to generate numbers in these fields which means that you open yourself to a slew of errors when you are trying ot handle that in your programs. for that reason alone adding TEXT to a NUMERIC field is an extremely bad idea. the second thing, which in my mind is not a pressing issue, you are adding an entire byte to a field to make an "on" or "off" distinction. this would be far better handled by the database as a bit field, which coincidentally only takes up a bit. thats what its for. in the past, maybe it was the way things are done, but it simply is just bad practice with modern databases to do what we are doing. no single field should contain two bits of information. it just doesnt make sense and can (and has) lead to problems. i cannot take your word on it, because this is something that i know for a fact and have experience multiple times. they are called job NUMBER, project NUMBER, base contract NUMBER, and employee id NUMBER for a reason. the database allows you to keep numeric data for a reason...choosing not to do so just leads to integrity problems, which is what we are seeing right now with this record, and have seen in the past. as for the argument that you would never want to do math with these numbers, i have one against that...because you most certainly would want to do math with them. everytime you add a new job, or employee, or base contract, or subcontract number to the database it is FAR BETTER to allow the database to handle determining what that number should be...if they are stored as text fields, it cannot do that...so you end up having to determine what the next record number should be, which means that you open yourself up to a lot of error possibility because of the fact that you are getting max and trying to ADD one number to it to make the next number. if two people tell it to add a record at the same time, then they end up with the same number, and you end up with BAD DATA. go figure. if its a numeric field, and two people try and add a job at the same time, the database gives each job a different number. -----Original Message-----From: Rustin [mailto:rblackwell] Sent: Monday, March 03, 2003 10:33 AMTo: tblackmonSubject: RE: Website errors again - ERROR FIXEDIncorrect. All the 'more' reasons would be invalid or flat out bad. If it were always treated as text as it is THEN he would have never had the problem. Have you ever seen that problem with looking up by last name? No? Good same deal. It is a very simple thing treat numbers like numbers, text like text. It is fine if you don't understand just take my word for it. If multiplying one value in the field by another makes no sense at all then it ain't a number.-----Original Message-----From: Tony Blackmon [mailto:tblackmon] Sent: Monday, March 03, 2003 10:17 AMTo: 'Rustin'; 'Gordon Cantor'; jkerseySubject: RE: Website errors again - ERROR FIXEDActually, there are more reasons for setting a field type to NUMERIC even if you are not going to perform calculations with it! The biggest one is for DATA INTEGRITY. The very problem Gordon is having would not have occured AT ALL if his field were saved as a numeric data type. -----Original Message-----From: Rustin [mailto:rblackwell] Sent: Monday, March 03, 2003 9:59 AMTo: 'Gordon Cantor'; tblackmon; jkerseySubject: RE: Website errors again - ERROR FIXEDYou have base contract number field set up as a number? You're going to divide or multiply it? Get it's sq root??? No? Then it is not a candidate for a numeric field. It is a name that happens to consist of numbers.-----Original Message-----From: Gordon Cantor [mailto:gcantor] Sent: Monday, March 03, 2003 9:21 AMTo: tblackmon; jkerseyCc: 'Rustin'Subject: RE: Website errors again - ERROR FIXED Tony, The “ACCESS’ search worked fine for all the records. This problem was not with the Project Experience SQL data, but rather a simple coding error on my part- related to the alpha-numeric empid’s in the Timesheet Database. The error has been fixed. FYI, the error a few weeks ago was due to alphabetical characters in the base contract number field within one record. I did check today to make sure that there were no more alpha characters in the base contract number field within any of the records. -----Original Message-----From: Tony Blackmon [mailto:tblackmon] Sent: Friday, February 28, 2003 3:15 PMTo: jkerseyCc: 'Gordon Cantor'; 'Rustin'Subject: FW: Website errors again Gordon, I sent you this one once before...you have a field that should all be numeric and it has a letter in it somewhere in the project experience stuff. I think searching for the word access is what was complained about before, you might want to try that. Search for 'ACCESS' and then click NEXT 50 RECORDS or whatever the button says. -----Original Message-----From: McCrystal, Michael [mailto:mmccrystal] Sent: Friday, February 28, 2003 3:09 PMTo: 'Tony Blackmon'Subject: Website errors againGetting this error message now....... seems like a bad week for the website. Error Occurred While Processing RequestError Diagnostic InformationOLEDB Error Code = 245Syntax error converting the varchar value 'T0001' to a column of data type int.SQL = "SELECT * FROM timesheet.dbo.empinfo, timesheet.dbo.divnames, timesheet.dbo.offices WHERE 61256 = timesheet.dbo.empinfo.EmpID and timesheet.dbo.empinfo.divisionNbr = timesheet.dbo.divnames.divisionNbr and timesheet.dbo.empinfo.OffNbr = timesheet.dbo.offices.OffNbr"Data Source = "PROJECTEXPERIENCE"The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (455:1) to (455:54) in the template file D:\INETPUB\WWWROOT\PROJECT\PROJECT_EXP\JOBLISTFORM.CFM.Date/Time: 02/28/03 15:03:18Browser: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.2) Gecko/20021120 Netscape/7.01Remote Address: 66.47.228.228HTTP Referrer: http://corp.wilbursmith.com/index.cfm Please inform the site administrator that this error has occurred (be sure to include the contents of this page in your message to the administrator). |
|
|
fluid
Starting Member
2 Posts |
Posted - 2003-03-03 : 11:30:15
|
| oh yeah, forgot to mention the T you see in the record that caused the break. the reason for that is that he is keeping the field as a char field...and is appending a T onto the front of all records that represent a temporary record. can you say "bit field" anyone? |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-03 : 12:13:58
|
| Well, the topmost response makes 100% perfect sense to me for all the reasons you listed. The only way an argument can be made against storing them as numbers is if the T records got entirely new values if the T is removed, and I'm sure they don't. And using prefixes in the data, as you already mentioned, is very poor design and easily broken. Unfortunately it does not seem like your coworkers understand reason anyway, so I don't see what good it will do to have anyone here tell you that you're right and they're wrong. The real issue here is the "logic" behind their objections; it is COMPLETELY misguided and makes entirely the wrong argument. If they really understood what they were talking about they'd realize it will be a LOT MORE work to increment a character column.The only time it makes any sense to store numbers as text is if leading zeros need to be preserved, and usually this is only the case when the data is NOT generated by the system, for instance, a U.S. Zip code or Social Security number, and some international phone numbers. Since these are codes anyway, they don't have significance as numbers even though they only contain digits. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-03-03 : 13:11:41
|
| Must be a lot of fun working with this guy..how condescending.I hope you don't report to this guy.Does he want to use char Over varchar as well?I wonder what his take on nulls is then?Good LuckBrett8-) |
|
|
|
|
|
|
|
|
general question about data types...