When to make a field a number 1

[postermmxvicom]

Don't make a field a number, just because you are going to store numbers in it. Because, one day, it just might need to store a letter also. Learning this the hard way, right now.

Example: Numeric tracking numbers - sometimes, things get messed up and now they need #####A and #####B. And that's the way it's gonna be.



Do make a field a number if you are going to do calculations with it.


If this thinking is wrong let me know. I know that most of you guys who post here are super good at this stuff. This post is for the future-past me who needs to hear this. Gotta go, the storm is coming.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[RoyVidar]

Agree - and that's what I learned ages ago too, if you're not going to do mathematical operations with it, store it as text.

That would also reduce the "how to keep leeding digits" questions ;-)

Roy-Vidar

 

[Remou]

I think the rule you are looking for is more like "do not use fields that are used by users as key fields." This is why autonumbers are often good, the user need never know they exist and so cannot suggest adding letters.

 

[postermmxvicom]

That would also reduce the "how to keep leeding digits" questions

Thanks RoyVidar! I hadn't considered that possibility.

do not use fields that are used by users as key fields.

I definitely agree with that. Fortunately, I learned that one (from this forum and other places) before I started coding the database. I believe it has saved me much grief. That and this business is so crazy, it didn't take long to find out unique numbers with meaning aren't that unique around here

I think the corollary to that one is "Never reveal the key field to the end user, or it will gain business meaning and they will use it."

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[postermmxvicom]

the user need never know they exist and so cannot suggest adding letters

This was actually a tracking number they already had for business, and I, regrettably, set the field to number But, as I said above, fortunately, did not make it a primary key

Should have included this in the above post, but I am too quick to hit that submit button.


One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[fneily]

Don't make a field a number, just because you are going to store numbers in it. Because, one day, it just might need to store a letter also." This is both a design and normalization problem.
With a little design thought, why not make two fields? Say you want a number attached to an alpha and you want to increment the numeric part? You'd have to use some functions to parse out the number, increment it, concatenate it back....
Two fields, one the numeric, the other the alpha. Then in forms, queries, reports who'd concatenate.
And actually, this is normalization. All tables should be normalized or who'd run into problems such as the one you mentioned.

 

[postermmxvicom]

...You'd have to use some functions to parse out the number...why not make two fields...one the numeric, the other the alpha.

Interesting point. Funny thing is, I made it that way from the beginning. It is already two fields just like that, but now, the numeric part needs to have letter. Haha! Jokes on me

How would you handle leading 0's as RoyVidar pointed out? Is using functions to increment a number that bad? I have a beginners knowledge of normalization. Does this really violate normalization to store an numeric tracking 'number' as text because it may one day be alphanumeric?

if you're not going to do mathematical operations with it, store it as text.

RoyVidar, would fneily's point quoted below meet your threshold of 'mathematical operations'

and you want to increment the numeric part?

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[fneily]

the numeric part needs to have letter". This wasn't foreseen in the original design? The majority of the time developing a database is the design mode - pencil and paper, no computer. Now, I'll admit, you have to be a prophet to forsee all future questions. But that's why alot of time in design mode.
Leading 0's can be taken care of with formatting.
"Is using functions to increment a number that bad?" There is no response to a question like that. But officer, I only had two drinks.
"Does this really violate normalization to store an numeric tracking 'number' as text because it may one day be alphanumeric?" Yes. That's why I mentioned it. See the original papers of Ted Codd, creator and namer of normalization.
"I have a beginners knowledge of normalization." You either know normalization or not. Since this has to be done to every table before any data is entered, then knowing a little(?) normalization is inadequate. These are the rules of Access. Not mine.
If you look at all the questions in all the Access forums, so far in my analysis, over a third of the problems are due to not normalizing. Unfortunately, some people solve the question without explaining the tables are designed incorrectly.

 

[postermmxvicom]

This wasn't foreseen in the original design?

No, and because of the nature of the data - I left many things wide open while maintaining good design. However, the description of my project is essentially - "make a program that bends to us - not the other way around." And since that design was made, a key employee was replaced. This new key employee, uses additions -A and -B to solve issues that were, without exception, previously handled by issuing the next available number (even if that made a products run composed of non-sequential numbers).

My job is to accommodate them. Now, I know someone might say, "Well, part of your job is to educate them on what the right choice is." That's a wonderful sentiment, but not the reality of this situation.


On a different note:

Don't some tracking 'numbers' force you to store them as text or use functions? i.e. 12A73YTWE7ET6Q and the like?

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[fneily]

Nature of data" borders on philosophy and dealing with humans on psychology. Neither of which has to do with designing relational database per se, or by de facto.
"make a program that bends to us - not the other way around." That was one of the original reasons Codd imbarked on a new way of database design. An hierarchal database has just those problems. A well design relational database minimizes, if not eliminates, that very problem.
"Don't some tracking 'numbers' force you to store them as text....12A73YTWE7ET6Q...". Yes. But that wasn't your original concern.
"reality"??? I'll let the physicists at the LHC (Large Hadron Collider) handle if that even exists.
Of course, it's hard to argue "Whatever, it works.

 

[Olaf Doschke]

I would not handle this as a change of the existing field. At least not only. Put up a new field alphanumeric and for ease of use you could copy the old numeric only tracking numbers into this new field, too, as long as it's needed. But you can still use old functionality on old tracking numbers this way. You might split the old tracking number field into an archive 1:1 related table with key + numeric tracking number only.

It's okay to store numeric values in text fields, if the nature is not mathematical. That's not only valid for tracking numbers, but also telephone numbers or poastal codes, not only because they could overflow numeric fields.
But you did not wrong for what you knew at that time, now the demand changed and so it's a database and code change.

Sometimes it's important to have the historic version, be it simply to recover an older backup. Therefor I'd be cautious with actual field alterations of that kind. And yes, you would have less trouble if it always would have been an alphanumeric/character field.

Bye, Olaf.