I've id values for items which i need store. At this time they all are integers, but I am unsure when the data provider later on will introduce letters or symbols into that mix, so I am discussing whether or not to store it now as integer or string.
Exist performance or any other disadvantages to saving the values as strings?
Unless of course you actually need the characteristics of the integer (that's, a chance to do arithmetic), then its most likely easier for you to keep product IDs as strings. You won't ever have to do anything like add two product IDs together, or compute the typical of several product IDs, so there's no requirement for a real number type.
It's unlikely that storing product IDs as strings may cause a measurable difference in performance. While you will see a small rise in storage size, how big an item ID string will probably be much more compact compared to data within the relaxation of the database row anyway.
Storing product IDs as strings today could save you much discomfort later on when the data provider decides to begin using alphabetic or symbol figures. There's no real downside.
Don't consider performance. Consider meaning.
ID "amounts" aren't number except that they're written by having an alphabet of numbers.
Basically have part number 12 and part number 14, what's the distinction between the 2? Is a component number two or -2 significant? No.
Part amounts (and something that does not have models of measure) aren't "number". They are just strings of numbers.
Zip codes in america, for instance. Telephone numbers. Social security amounts. These aren't amounts. During my town the main difference between zipcode 12345 and 12309 is not the length from the house to downtown.
Don't conflate amounts -- with models -- where sums and variations mean something with strings of numbers without sums or variations.
Part ID amounts are -- correctly -- strings. Not integers. They'll not be integers because they do not have sums, variations or earnings.
It truly is dependent on which type of id you're speaking about. Whether it's a code just like a telephone number it might really be superior to utilize a varchar for that id after which have your personal id to become a serial for that db and employ for primary key. Inside a situation in which the integer don't have any statistical value, varchars are usually prefered.
I am unsure how good databases are in evaluating whether one string is more than another, enjoy it can with integers. Consider using a query such as this:
SELECT * FROM my_table WHERE integer_as_string > '100';
The area an integer would occupy would me a smaller amount than the usual string. For instance 2^32-1 = 4,294,967,295. This could take 10 bytes to keep, while the integer would take 4 bytes to keep. For any single entry this really is not so much space, however when you begin in the millions... As numerous other posts suggest you will find other issues to think about, but this really is one disadvantage to the string representation.
I have just spent the this past year handling a database which has just about all IDs as strings, some with numbers only, yet others mixed. Fundamental essentials problems:
- Grossly restricted ID space. A 4 char (digit-only) ID has convenience of 10,000 unique values. A 4 byte number has convenience of over 4 billion.
- Unpredictable ID space coverage. Once IDs start including non-numbers it might be difficult to predict where one can create new IDs without collisions.
- Conversion and display problems in a few conditions, when scripting or on export for example. When the ID will get construed like a number and there's a number one zero, the ID will get changed.
- Sorting problems. You cannot depend around the natural order being useful.
Obviously, should you exhaust IDs, or don't understand how to create new IDs, your application is dead. I would recommend that if you cannot control the format of the incoming IDs you will want to produce your personal (number) IDs and relate the consumer provided ID to that particular. After that you can make sure that your own ID is reliable and different (and number) and still provide a person-viewable ID that may have whatever format your customers want, and does not even need to be unique over the whole application. This really is more work, but when you'd experienced things i have you'd know which approach to take.