Consensus? MySQL, Signed VS Unsigned Primary/Foreign Keys

views:

answers:

+4 Q:

Consensus? MySQL, Signed VS Unsigned Primary/Foreign Keys

Hello, I have seen a couple threads about what everyone was doing now a days in regards to signed vs unsigned key values. It would seem that unsigned is optimal since it allows twice the number of rows for the same cost. Are there any benefits with signed keys?

Is there any standard consensus? Its seems like signed is the answer since "it has always been that way"

+2 A:

I prefer to set my columns as unsigned if they cannot be negative. Besides the range factor that you mentioned, it also makes it clearer when reading the table definition.

Daniel Egeberg 2010-06-28 23:15:46

A signed 32-bit int can represent over 2 billion numbers so I suspect the issue is moot for most people. That said, I often use negative numbers when I want to indicate that a record is purposefully orphaned.

Rodrick Chapman 2010-06-28 23:18:48

GAHH! Sorry, I work with an application where someone (no longer with the company) thought this would be a good idea. Literally, at least once a week, we find some kind of issue (either challenge with new code, or bug in existing code) caused by this crazy design decision. **You should not give primary keys meaning beyond the key value itself, and you should never change primary key values**. If you want to indicate soft-delete, have a status column or a softDeleted column. Because it was in a fundamental piece of the software, we're only just now fixing it, as part of a major rewrite.

gregmac 2010-06-29 03:34:16

@gregmac s'ok, I knew it would be controversial. Quite ironically, a better word that 'orphan' would have been 'existential'. It was an ironic choice of terms since I came up with the scheme in order to represent people that I only knew must exist. My application allows a user to say that Bob and Charlie are brothers but internally stored them has records whose parent(s) columns both referenced records with negative keys. If someone added Jane as Bob's mom I could update Charlie as well. Generally I use the pattern when I want to say that there exists some thing that other things must be near.

Rodrick Chapman 2010-06-29 04:24:14

Also, I'm very familiar with the doctrine that primary keys should not have meaning. A better maxim is that meaning should be encapsulated. PK's should not mean anything to the application layer but they do mean things to the database (management system) itself. In the scheme that I described above I'm simply using the PKs as metadata. Incidentally, I discussed this here before: http://stackoverflow.com/questions/2023988/modeling-existential-facts-in-a-relational-database

Rodrick Chapman 2010-06-29 04:27:21

ansaurus

tags:

views:

answers:

Consensus? MySQL, Signed VS Unsigned Primary/Foreign Keys

related questions