0

i'd got a general question.

I very much like URLs resulting in generated pages like that:

www.example.com/order/1e4fk678

Where 1e4fk678 is the variable posted to a php file generating the output.

My question is: What would be the best method to create the database table. An ID with a primary key auto incertment just generates IDs like 1, 2, 3, ....

So what is the best way to use a unique id that as well results in fast database tracking/traces?

Thanks

Arnold Daniels
  • 16,516
  • 4
  • 53
  • 82
Jimmy Tschonga
  • 57
  • 1
  • 4
  • 8
  • I'm not really sure what your goal is. Do you want to use the ID's from your URLs ("1e4fk768" etc.) as primary ID's in the database? – Manuel Mar 04 '13 at 13:54
  • Actually yes. If this is senseful. I don't like the identifier to be a simple id like 1,2,3 – Jimmy Tschonga Mar 04 '13 at 13:57
  • I assume that you don't want to have follow up numbers, because you don't want the user to go to obfuscate the identifier. Though a valid reason, it shouldn't be you're primary defense. – Arnold Daniels Mar 04 '13 at 13:59
  • I sure do check if a user is the owner of the data. But still i don't like upcoming numbers on orders eg anyways. – Jimmy Tschonga Mar 04 '13 at 14:01
  • A lot of good stuff u wrote. But sadly no one has answered my question yet;) – Jimmy Tschonga Mar 04 '13 at 15:03

2 Answers2

3

Your DBMS already generates unique (auto_increment) identifiers. What you've not said in your question is whether you want the value presented in the URL to be non-predictable.

If not then you can simply do a base conversion on the generated id.

If you need the value to be non-predictable then pad it out as a string (most encryptions algorithms will do this automatically up to a multiple of the block size) and use reversible encryption to encode the auto-increment integer - and add some validation to detect brute force attacks. The method does not have to be reversible, but most methods of making the encryption non-reversible (generating a hash, using the data as an encrpyiton key, using a random encryption key) introduce a risk of collisions, requiring the encrypted data to be much larger.

If the reference is supplied remotely then it's simply a matter of adding a unique index on the data. But you still need to think about how you deal with collisions.

Using a random value has some merit for low data volumes - you can detect a collision at generation time - but the cost of verifying that the generated value does not collide increases at a rate of at least O(logN) (for an indexed field).

update

So using [a varchar instead of an integer] as a primary key will not slow down my database to medieval?

No - and that's the least of your worries.

symcbean
  • 47,736
  • 6
  • 59
  • 94
  • I think this goes best for a complete answer now. +1 for adressing the initial problem AND speaking of resulting problems :) – Manuel Mar 04 '13 at 14:21
  • Yes, but if i want the valuable to be non-predictable, how can i make sure that zhere is no collusion? That the uniqueid is really unique? – Jimmy Tschonga Mar 04 '13 at 14:35
  • I thought I'd explained that. At some length. uniqid() can produce a resonably unique value - but you need to add some additional code to ensure it's unique across a cluster. Hence recalculating and detecting brute force attacks. Read the discussion on uniqid() in the manual. – symcbean Mar 04 '13 at 15:32
  • It's (almost) never important to unsure uniqueness, you just need to have a very small chance that 2 random values are the same. – Arnold Daniels Mar 04 '13 at 16:40
  • So u think i can just use mysql UUID() to get the job done? Don't u think i should check back? If yes, how can i do that? – Jimmy Tschonga Mar 04 '13 at 16:58
  • @Arnold Daniels sorry, actually i'd prefer the unique id produced by the php function – Jimmy Tschonga Mar 04 '13 at 17:09
  • "It's (almost) never important to unsure [ensure?] uniqueness" - erk! That's the *defining* feature of a **unique** identifier. – symcbean Mar 04 '13 at 21:21
0

You can actually use a String column with a unique constraint in mysql - or even as a primary key column. Just say:

`id` varchar(..) PRIMARY KEY

in your table definition.

Have a look at this question, it deals with strings as primary keys and their performance issues: MySQL - using String as Primary Key

Community
  • 1
  • 1
Manuel
  • 483
  • 3
  • 14
  • Okay. So using it as a primary key will not slow down my database to medieval? And how can i check if the unique string is really unique? What kind of field type qould u suggest? Varchar 255? – Jimmy Tschonga Mar 04 '13 at 14:11
  • Well make it as long as your IDs are. Did you take a look at the linked question? If I understood right the performance issue shouldn't be that important and I think this would be a sensible case to use a string primary key. As for checking if it's unique, do you want to do that in PHP before calling the database or do you want the database to check it for you? In the second case, a primary key already checks and fails on trying to insert a duplicate entry. – Manuel Mar 04 '13 at 14:14