Difference in integer length in code and database

[fcarlier]

Hi,

Quite often, I notice that there is a difference between the data type being used in the mysql database and the program code. For example, most of the time, the C# code itself will declare most integers just as int -- being 32 bit (4 byte) signed integers [1, 3, 4].

However, on the database side, some fields are declared as, for example, smallint, which is a 2-byte signed integer [1, 2]. An example of such a field is Mount.DocCategory.

Why is this a problem? Because when you assign a big value to an int in the program code, it may get truncated when you store it to the database. As an example, storing int.MaxValue to a field of type smallint will return short.MaxValue when you load it from the database again. Quite obviously, this isn't good!

It happens that the Unit tests I'm writing detect this. There are a couple of possible solutions:
1. Just ignore the problem.
2. Adjust the types used in the program to those in the database (introducing long, short, whatever)
3. Adjust the types used in the database to those in the program
4. Standarizing on int

Any comments? If there is a consensus that some action should be taken, I volunteer to gradually do so -- starting with the imaging modules (that should be covered by the unit tests).

Frederik.

[1] http://en.wikipedia.org/wiki/Integer_%2 ... science%29
[2] http://dev.mysql.com/doc/refman/4.1/en/ ... types.html
[3] http://msdn2.microsoft.com/en-us/librar ... S.71).aspx
[4] http://msdn2.microsoft.com/en-us/librar ... int32.aspx

[jordansparks]

We did recently eliminate some of this discord by eliminating all the mediumints (3 bytes) in the database and turning them into ints (4 bytes). I think we need to do this for all remaining primary keys, even those which are currently 2 bytes. This will provide consistency for primary keys and allow the random primary keys to work for all tables. Mount.DocCategory did not have a good description, so I just changed it. It is a FK to definition.DefNum. That is an example like the one I described above, where I think it would be best to make definition.DefNum and all of its FK's into full 4 byte ints. But there are a lot of them, and I don't think there's a huge rush. We could start with definition.DefNum, and change its FK's gradually.