What will happen if primary key value exceeds range

[fahien]

hi everyone...first let me tell you i am a newbie in the world of software development. so my question may seem very annoying to you as you are all expert in this field. though i appreciate your help. now lets go to the point.

i was planning to make a mobile data backup system for which i planned to use mysql database. for simplicity i was considering only contact backup. i created two simple table

USERINFO([U]userID[/U], password)

and

CONTACT([U]contactID[/U], userID, contactNo)

where userID & contactID are integer value auto incremented. my plan was that each contact will go into CONTACT table with different contactID. and the foreign key userId will be used to get all contacts of a user. now i guess my solution is not good. because a user may have 1000 contacts. if our application is used worldwide we will have a lot of user and the contactID field may at time get out of range. so in these case what should be our approach to design the database?

i am sure all of you out there have the solution for this type of problem. please give me your suggestion on this.

[smantscheff]

If you use 32-bit integers for the contact ID field you may store up to 2^32-1 different contacts. That should be sufficient for all practical purposes.

[fahien]

i think you are right for small scale. but suppose we have 1000 contact on average for each individual. 2^32-1 = 4294967295. now dividing it by 1000 we get 4294967. which means that by this way we will be able to store contatcs of only 4294967 people. now does it seem practical to you?

[smantscheff]

If you use a DBMS like MySQL or the like you can also use 64-bit integers as primary keys. This gives you 18.446.744.073.709.551.615 possible contacts - is that sufficient? If not and you are planning on a galactical scale use a char field with 50 (or so) characters and increment the values by your own routine instead of an auto-increment value.

[fahien]

If you use a DBMS like MySQL or the like you can also use 64-bit integers as primary keys. This gives you 18.446.744.073.709.551.615 possible contacts - is that sufficient? If not and you are planning on a galactical scale use a char field with 50 (or so) characters and increment the values by your own routine instead of an auto-increment value.

i really liked your answer. i didn't know that the id can hold upto 64bit value. it will help me a lot. and the technique for you described in case galactical scale data was also good. i got my answer. but i would really like to know is there any possibility in huge scale database (consider facebook, gmail, yahoo) to overflow the 64 bit value?

[smantscheff]

Some simple arithmetic shows: if all human beings start adding contacts with the steady rate of one contact per second, night and day throughout, even on holidays, than you will be out of unique IDs after 84 years. You really like to plan it big, do you?

[adam_k]

SQL can handle up 18,446,744,073,709,551,615 with BigInt. If this isn't enough, you can always add a new column in the table and the key and use both fields as ID.
Instead of this, why aren't you woried that you can store only contact number and not everything else in the Google Contacts API?

PS: Good luck convincing everybody to share their private data with you.

[fahien]

Some simple arithmetic shows: if all human beings start adding contacts with the steady rate of one contact per second, night and day throughout, even on holidays, than you will be out of unique IDs after 84 years. You really like to plan it big, do you?

you are right. thank you. got my answer. thanks again to all for their help. really appreciate it.