?
Solved

Alternatives to bigint for large file primary keys, wanting to stay with int

Posted on 2014-02-09
5
Medium Priority
?
412 Views
Last Modified: 2014-02-10
My app, if successful, could conceivable have 100,000 users, who use it 10 times each, generating 1000 records each time, thus generating 1 billion int type primary keys.  So conceivably the app could eventually generate enough records to exceed the limit of the int type (2B+).

Another feature is that most of these records would be effectively archived within 90 days.

GUID is overkill for sure, so the alternative is bigint.

But since bigint does not appear to be natively handled in Javascript (though there are libraries for that purpose), I was wondering if there is some SQL strategy for handling these situation, so I could stay with int.

Or would that be more complicated that using the appropriate javascript tools.

Any suggestions about this would be appreciated.

Thanks!
0
Comment
Question by:codequest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 600 total points
ID: 39846500
By far the easiest solution is to use GUID - thats what they're for... there is no such thing as overkill IMO... I would never use anything other than GUID no matter the size of my DB.

However if you can't get your head around that :) you can use a multi-part primary key e.g. year, month, count or user, count - but I would never recommend it as it is going to cause complications at every point in your system. You'll have to default it yourself, and manage it in code yourself.
0
 
LVL 2

Author Comment

by:codequest
ID: 39847139
Thanks for the input.  I think you answered the question by saying multi-part key on top of int.  But it seems that dealing with bigint in javascript would be easier than that.  

But continuing the reasoning what about...

>  GUID taking way more disk space than bigint?

>  Some kind of awful partitioning of the DB to allow continued use of int?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1200 total points
ID: 39848085
GUIDs are a royal pita to deal with, aside from causing extremely severe fragmenting in indexes. After someone's tried to read a GUID for you to type in for, say, the third time, you'll be kicking yourself for not finding some alternative -- any alternative -- to that.

You could use a combined ( tinyint, int ) key.  The tinyint would start at, and default to, 1.  IF the int value is ever about to overflow, you change the tinyint's default value to 2, and reset the int value to 1.  You can create code that runs every day, or on another applicable schedule, which checks for that condition and makes that change if needed.  [Since tinyint holds 255 different values, the combined tinyint, int gives you roughly 500B unique key values.]

There are other ways, too, of course.  

Btw, make sure you consider the proper clustered index separately from the primary key.  Often the PK is not the best choice for the clustered index.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 39849089
eventually generate enough records to exceed the limit of the int type (2B+).
Actually it is 4B, all you need to do seed it to  -2,147,483,648.
0
 
LVL 2

Author Comment

by:codequest
ID: 39849326
I blew the original assumption.  Javascript can handle very large numbers, it just can't do math well in them.  So I can use bigint and long for my primary key.  

Suggestions were useful otherwise, points granted accordingly,
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question