GUIDs, What can they be for?

MS SQL GUIDs

In my last post, I talked about GUIDs and if they were good or bad items to use for Primary Keys, today, lets talk about when we would want to use a GUID.

Usages

So, why would someone want to use a GUID compared to a integer primary key? I can think of a couple of good examples for this…

When to Use

Large Franchises (AKA Big Data)

Well, lets assume that we are working for a project with a “large” franchise basis (hypothetically like either a sub-shop or hotel), and lets also assume that each of the store fronts have to send in a daily accounting of what they have sold as a summary (For sub-shop, how may of each type of item and for the hotel, how many rooms and room occupants such as “12 rooms with 2 occupants were rented today”).  Now, I have no problem envisioning a solution that would take the data from a file and do an individual import of that data into a table that contained integer values for primary keys, and linking that to a specific record for each store.  To me, this would look something like this below.

Integer Possible

When I go to buy something from a certain sub-shop they usually make my sandwich and then I go over to the register where they ring me up with all of the items that I purchase (such as a foot-long roast beef sandwich, a bag of chips, a large drink, a cookie and a milk) and they will usually mark it as 1 – 12″ roast beef combo (LG), 1 – cookie, 1 milk. At the end of the day, the store owner will want to look at all of the sales for the day, but he probably won’t want to look at each individual sale, he will want to look at a summary, such as X – 12″ Roast Beef Combo (LG), Y – 12″ Roast Beef Combo (MD), Z – 12″ Roast Beef Combo and so on. As such, I am expecting that the table structure would look something like this…

As the cashier starts ringing me up, they create a POSSales record, and for each item that they add to the order becomes a new record within the POSDetails table. At the end of the day the boss clicks a button that will go through all of the POSSales Records for a specific day and summarize the POSDetails into the DailySummary table and then update the POSDetails POSDetailsDailySummaryTransactionID to be the new record. After they have reviewed it, they press another button that will transmit the details to the corporate office which will then assimilate those summaries to their own system.

When the Corporate Folder receives this file they then insert their data to their database, which I can assume to look like this…

This would work in a “perfect” world, but alas, we don’t live in a perfect world and there will be times where the Corporate will see something “strange” such as selling 1,000,000 Roast Beef Combo (LG) for a single store, and they would probably have to ask a question. In this construct, they would have to call up the owner of the franchise and ask them, please send me a detailed listing of all Roast Beef Combo (LG) for the date of mm/dd/yyyy that you transmitted to us on mm/dd/yyyy, and then the franchise owner would have to open his records and then look at it and report back. The issue is that as the data was translated from DailySummary to SandwichesSoldPerDay any reference to the POSDetails was lost.

GUID Approach

If we did a slight change to the construction of these two databases, such as converting the fields POSDailySummaryTransactionID, DailySummaryTransactionID and SandwichTransactionID to UNIQUEIDENTIFIERs, we could have just picked up the data from the file and plopped it into the Corporate database, maintaining the referential link to all of the POSDetails for the daily summary in the unconnected database. Within the Application, we can also include a command that would take the DailySummaryTransactionID and return all the POSDetails records that are associated with it, allowing corporate to investigate without the need of contacting the Franchise Owner (in case he is in some sort of fraud) and without having to constantly transfer all of the POSDetail Records for every day.

Here is how the tables would be affected for the client side…

And this is the changes necessary for the Corporate Side

Granted, there are ways to also do this in the Integer methodology, but there are also some serious gotchas that could occur.

Semi-Random Data

There are also times that you will want to send across semi-random data, such as in a Password Reset process, where the user will have to click a link that you don’t want to have living long afterwards, or have a logical pattern. In such an instance, I would write the table as similar to this…

This allows for multiple options, my personal favorite would be when the user clicks the forgot password link, it creates a new GUID and emails it to them as part of the password recovery phase, after the user has successfully reset their password, you can then store a NULL into the table making the previous email link invalid. You can also set it up so that if they have waited for more then 30 minutes of time from the UserResetRequestDate, we can then assume that this is not an appropriate connection and reset the GUID and have it

Really LARGE Data

When working in a table that you expect would go past the typical size that is possible for an integer type (2,147,483,647). In my limited exposure, I can’t think of anything that would easily go past that number simply.

Why Not to Use

Small Data

If you are building something that is anticipating to be living and breathing on a single server where flexibility is and user friendliness is important, I would be more inclined to handle Integers. As we saw in the last post, they have a very slight advantage when it comes to performance, but it has a huge advantage in handling physical spaces. For the small to mid sized business (SMB), this is an important trait, but I wouldn’t use this one piece as the pivotal point of my argument. Instead, the more pivotal point would be easier for users to read. In SMBs, the biggest issue revolves around people. Large organizations I would expect to have teams of people that are extremely well versed in how to build software maintaining the data and handling update/upgrades, while in the SMB environment, most of the time it will be individuals who are doing this in addition to their normal jobs, so understanding how a GUID Works probably won’t work out to well for them.

Normal Data

Unless there is a particular reason why you would have a two identical Sales Orders or AR Invoice numbers, then I would stick to a standard Integer Primary Key.  Typically, if the primary key is going to be human readable, it probably should not be a GUID for a primary key.

Summary

Basically, if I am anticipating a database structure that will cross multiple databases, or have something that will possibly walk out to the general public that I don’t want to have a sequential or repeatable URL I should use GUIDs. If I am expecting users to be able to interact with the data easily, or drive space is a concern, I would expect to use Integers, otherwise, they both are awesome choices for the Primary Key of a database table.

18 comments

  1. My husband and i ended up being now lucky when Peter could complete his studies while using the ideas he made through the web site. It is now and again perplexing just to choose to be giving out methods that some people could have been selling. And now we do understand we now have the blog owner to thank for this. Those illustrations you made, the simple blog menu, the friendships your site help foster – it’s all amazing, and it is letting our son in addition to us do think this subject matter is amusing, which is certainly especially mandatory. Many thanks for the whole thing!

  2. My spouse and i got now ecstatic when Raymond managed to carry out his inquiry because of the precious recommendations he grabbed in your web pages. It’s not at all simplistic to simply find yourself releasing steps which often most people could have been trying to sell. We fully understand we need you to appreciate because of that. These illustrations you made, the straightforward web site navigation, the relationships you will make it possible to engender – it is everything extraordinary, and it is aiding our son and our family reckon that that issue is fun, which is certainly rather fundamental. Many thanks for all!

  3. I am only writing to let you be aware of what a extraordinary discovery our princess enjoyed checking yuor web blog. She even learned a lot of pieces, most notably how it is like to have an awesome teaching nature to get many others without problems have an understanding of selected hard to do topics. You undoubtedly surpassed my desires. Many thanks for presenting these helpful, dependable, revealing and also fun tips on your topic to Kate.

  4. I’m also commenting to let you understand of the amazing discovery my cousin’s girl encountered reading your web page. She picked up a wide variety of issues, including what it is like to possess an ideal coaching mood to make other individuals smoothly know precisely selected impossible subject matter. You actually did more than our expectations. I appreciate you for presenting such great, trusted, revealing not to mention easy thoughts on that topic to Julie.

  5. My wife and i felt very more than happy Michael could carry out his investigations while using the ideas he came across out of your blog. It’s not at all simplistic just to find yourself freely giving guidance which other folks may have been trying to sell. And we also discover we need the blog owner to be grateful to because of that. The type of illustrations you’ve made, the simple web site navigation, the friendships you help create – it’s mostly great, and it is helping our son in addition to the family imagine that this theme is awesome, which is certainly exceedingly essential. Thank you for all the pieces!

  6. I together with my friends have been viewing the best secrets located on your web blog and so quickly got a horrible suspicion I had not expressed respect to the site owner for those techniques. All of the people had been for this reason very interested to study all of them and already have truly been loving those things. Appreciate your really being really considerate and also for deciding upon such fantastic useful guides most people are really desperate to understand about. My sincere apologies for not expressing appreciation to you sooner.

  7. I have to convey my respect for your kind-heartedness supporting visitors who have the need for help with this study. Your special dedication to getting the message all through was wonderfully interesting and has always empowered women much like me to realize their dreams. Your own warm and helpful tutorial can mean much to me and even further to my office workers. Regards; from everyone of us.

  8. I wish to convey my admiration for your kind-heartedness for people that need help with that concern. Your special commitment to getting the solution all through appears to be extremely insightful and have without exception made regular people much like me to attain their objectives. Your own invaluable hints and tips entails so much to me and somewhat more to my peers. Regards; from all of us.

  9. My wife and i have been really thankful that Jordan could complete his basic research through your ideas he gained when using the blog. It is now and again perplexing just to possibly be giving out guidelines the rest have been making money from. And we all remember we need the website owner to appreciate for that. The type of explanations you made, the simple website navigation, the relationships you give support to promote – it’s all fabulous, and it is facilitating our son in addition to us recognize that this concept is pleasurable, and that is wonderfully pressing. Thank you for the whole lot!

  10. I enjoy you because of your entire effort on this website. Kim really loves setting aside time for internet research and it’s really simple to grasp why. A number of us learn all about the compelling manner you render precious guidance on the web site and therefore recommend response from some others on the theme then our daughter is really discovering a whole lot. Enjoy the rest of the year. You’re performing a useful job.

  11. I simply desired to thank you very much once again. I am not sure what I could possibly have used in the absence of those techniques discussed by you directly on my subject matter. It absolutely was a very troublesome scenario in my view, but finding out your specialized approach you treated the issue took me to cry for fulfillment. Now i’m thankful for your information and in addition believe you comprehend what an amazing job your are providing teaching some other people using your webpage. I am sure you’ve never got to know all of us.

  12. I not to mention my buddies appeared to be reviewing the best tips and tricks from your web blog and so instantly developed a terrible feeling I never expressed respect to the blog owner for those strategies. All of the boys were definitely as a consequence passionate to read through them and have in effect actually been using those things. Many thanks for indeed being so accommodating and then for making a decision on this sort of high-quality issues millions of individuals are really needing to discover. My personal sincere apologies for not saying thanks to you earlier.

  13. I together with my buddies were found to be going through the good tips located on the website and so all of the sudden got a horrible suspicion I had not thanked the web blog owner for them. The boys had been as a consequence happy to read through all of them and have surely been taking pleasure in these things. Appreciate your being so considerate and also for picking out variety of beneficial guides most people are really desperate to learn about. My very own honest apologies for not expressing gratitude to you sooner.

  14. I would like to voice my gratitude for your generosity for individuals that really need help on that theme. Your personal dedication to passing the solution across appeared to be astonishingly interesting and has regularly permitted people like me to reach their endeavors. Your personal informative help and advice can mean so much to me and still more to my peers. Warm regards; from each one of us.

  15. magnificent submit, very informative. I wonder why the opposite specialists of this sector do not understand this. You must proceed your writing. I’m confident, you have a great readers’ base already!

Leave a Reply

Your email address will not be published. Required fields are marked *