What is the real diference between each one?
I think auto inc is easy to hack vs uuid
Uuid is slow than autoincrement in a query with to many records, but is there a huge difference in it?
A:
An increment ID is not "easy to hack" in itself, it just provides entry-points that are obscured (but not entirely hidden) when you use a big random ID. There still needs to be badly implemented and exploitable software for there to be a real danger. As you can see in the URL in your address bar, this very site uses incremental IDs with no problems.
Apart from security thoughts though, a random unique ID is sometimes helpful when you don't want users to easily guess the URL of other (albeit public) content. For example, on a real estate site, you may not want to offer the possibility of going "up and down" in the IDs, looking at competitors' entries, even though they could find them all through searching. A bit of obstruction might be a good thing.
Why not use both? A numeric auto-increment key for speed in indexing and relations; a random UID for outside access.
Pekka
2010-03-04 09:54:28
Yeah, I think I'm going to do that, use both, actualy I'm going to use the auto-inc as always but for the user we'll going to hash the id using base36, my problem as I say before is that we need to give to users a link to have acceso to their ticket, so if I use domain.com/tickets?id=1 is very predictable
ElAlecs
2010-03-04 16:08:40
A:
some thoughts:
- auto-inc: DB assures uniq ID, but you'll have to retrieve it or lose 'contact' to the data-record you just inserted.
- UUID: must be created 'outside' (not in db server, could be in app-server). ID is known and link to inserted record exists, but (very small, depends on uuid-ness of the uuid) collision risk on insert.
lexu
2010-03-04 09:55:10
A:
The primary key is very important from point of view of relational model
- uniqueness must be checked fast
- used in other table as foreign key
- used to join
The smaller the PK, the better. That's why numeric PK is the best.
If your concern is that it's easy to "hack", you can add an additional UUID as a natural key
- used only for "direct access" to the row
That's what I've seen in a couple of projects and it worked like a charm.
ewernli
2010-03-04 10:12:54
Yeah, I think this is the answered I'm looking for, so, a solution is have an auto-inc PK to use in the DB context and have a UUID key to external context.I have this dilemma because if I have a link like domain.com/tickets?id=1 to show a ticket for something, anyone can try to change the id to have access to everything, because is predictable
ElAlecs
2010-03-04 16:02:18
Yes. Note however that the definitive solution would be to use authentication and forbid access to ticket which you're not allowed based on some permission scheme. UUID are not really meant for security neither. But IMHO, an UUID to identify the ticket in the real world, and a numeric PK for the database is useful anyway.
ewernli
2010-03-04 16:17:25
A:
Take care of the LENGTH of your PK column... UUIDs and GUIDs are extremely long... strings. An INT or even BIGINT autoincement column can ensure uniqueness in a much smaller space.
BEWARE that autoincement columns have a few issues of their own around table management. If you truncate/drop-create tables then an auto-increment will be hard to maintain. ALSO, only 1 auto-increment column in MySQL is allowed for a single table.
If your data allows it, use some kind of HASH derived from the data for indexing and performance.
J Jorgenson
2010-04-06 20:22:31