> Multiply those rows by 4 bytes and you have 8 million bytes, or roughly 7.8MB of wasted space.
I have a hard time imagining any installation where this isn't a ludicrously small amount of space, particular in relation to the total data size.
As for using too big a type, another factor is to consider is "how easy is it to expand this later?" If it's easy, sure use a small type. If it's hard, err on the side of too large.
> Now, for reasons I can’t explain, people just love GUIDs in database designs.
There are some pretty good (or at least understandable) reasons for liking GUIDs for keys. Firstly you need to understand how the database generates IDs.
The first camp is typified by MySQL with it's auto-incrementing keys. This is super-convenient in many cases and generates ordered and mostly sequential keys but it comes at a cost in that you typically have to round-trip to the DB and insert something to get a key. This generates spurious round trips if you need this key as a foreign key in other entities.
The other school is sequences typified by Oracle. A sequence isn't otherwise tied to a particular table. You still need a round trip to the DB to get an ID but it doesn't require you to insert anything. Sequences are in-memory and generally fast (round trip notwithstanding). Sequences are mostly ordered and partially sequential depending on your setup (this is less true in a clustered environment).
So, GUIDs. They're 128 bit. Collisions are unlikely. OSs tend to come with decent GUID generators (so you're not just using a 32 bit PRNG to generate your supposedly 128 bit keyspace items). They require no DB round trip and they have one other property that can be important depending on your setup and your database: scattering.
If you have a partitioned database then you want to evenly spread your data as much as possible. There are many techniques for this that might or might not be data dependent (eg first character of email address has poor scattering properties but a good hash of an email address will probably be spread much more evenly). GUIDs neatly spread elements in your keyspace for very little effort.
> If you are using a surrogate key (and, ideally, one that is sequential)
I don't see sequential or ordered being strictly better properties for keys.
> You still need a round trip to the DB to get an ID
No you don't. Not sure about MySQL but Oracle supports returning generated keys as part of an insertion[1]. An additional round trip is not required as it comes back in the "success" response of the insert itself.
http://stackoverflow.com/questions/621884/database-developme...
To add some points:
> Multiply those rows by 4 bytes and you have 8 million bytes, or roughly 7.8MB of wasted space.
I have a hard time imagining any installation where this isn't a ludicrously small amount of space, particular in relation to the total data size.
As for using too big a type, another factor is to consider is "how easy is it to expand this later?" If it's easy, sure use a small type. If it's hard, err on the side of too large.
> Now, for reasons I can’t explain, people just love GUIDs in database designs.
There are some pretty good (or at least understandable) reasons for liking GUIDs for keys. Firstly you need to understand how the database generates IDs.
The first camp is typified by MySQL with it's auto-incrementing keys. This is super-convenient in many cases and generates ordered and mostly sequential keys but it comes at a cost in that you typically have to round-trip to the DB and insert something to get a key. This generates spurious round trips if you need this key as a foreign key in other entities.
The other school is sequences typified by Oracle. A sequence isn't otherwise tied to a particular table. You still need a round trip to the DB to get an ID but it doesn't require you to insert anything. Sequences are in-memory and generally fast (round trip notwithstanding). Sequences are mostly ordered and partially sequential depending on your setup (this is less true in a clustered environment).
So, GUIDs. They're 128 bit. Collisions are unlikely. OSs tend to come with decent GUID generators (so you're not just using a 32 bit PRNG to generate your supposedly 128 bit keyspace items). They require no DB round trip and they have one other property that can be important depending on your setup and your database: scattering.
If you have a partitioned database then you want to evenly spread your data as much as possible. There are many techniques for this that might or might not be data dependent (eg first character of email address has poor scattering properties but a good hash of an email address will probably be spread much more evenly). GUIDs neatly spread elements in your keyspace for very little effort.
> If you are using a surrogate key (and, ideally, one that is sequential)
I don't see sequential or ordered being strictly better properties for keys.