Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I recently learned about how relationships are defined in the database at work, and was wondering if this is a standard practice.

Let's say we have two processes: Process A, and Process B. Process B depends on the results from Process A, so there is a relationship that needs to be defined between a Process B run and a Process A run. This is how the relationship is defined:

TableProcessA:
Id

and

TableProcessB:
Id
ProcessAId

Now, up to this point, things make sense to me, but then things get a little strange to me and my understanding of table design. Whenever a row is created in TableProcessA or TableProcessB, a function is called that creates a globally unique id for each. So basically, all of the Id fields in TableProcessA and TableProcessB will not contain any matches because the Id's are not just unique to its table, but to the entire database.

My question is, how standard is this? I was brought up on the idea that each table should simply have an autoincrementing id that is unique just to the table and not the entire database.

4 Answers
4

This isn't that weird a practice. Those are called GUIDs, or Globally Unique IDs. The idea is that, given a GUID, you can tell exactly what piece of data the id belongs to because it will be unique everywhere. GUIDs are best used when you will be merging different sources of similar data; for example inventory for different stores.

I would do some research and find out as to why this is a common practice in your environment. Maybe it's needed, maybe it's not.

In principle having unique Ids across a database isn't too bad but its rather unusual in my experience. Unless there is a specific requirement for maintaining a globally unique Id keys, using the normal identity should be fine as the entity types or objects tend to be type specific and doesn't usually result in any inappropriate joins.

As an aside to your question I would strongly suggest not using GUIDs as primary or surrogate keys as it takes up 4x the space of an int. Now in the days of multi gigabyte disks you might says it's not important but when you have a few million rows of data it still takes resources to read from disk or transfer across a network, Even more so if they get included in any indexes. While I'm on the subject indexes, GUIDs are bad in primary keys due to there random nature and usually causes significant fragmentation.

Now it's likely to be too late for this database but keep it in mind for the future

Your processes can create records that contain the GUID as a reference to the process instance, but using the GUIDs as a key is not necessarily optimal.

I see three principal disadvantages of using GUIDs as database keys, and would avoid them unless you have some compelling reason to use them. Note that the globally unique property doesn't get you any advantages over using a key that is locally unique within a table, because you already know what table the data came from.

Inconvenience: It's not terribly easy to type in a GUID if you want to do an ad-hoc query of the database, for example:

The latter practically means you need a source of the GUID to cut and paste from.

2. Natural ordinality: An autoincrementing primary key has he side effect of naturally ordering your transactions in the order they were entered into the system. This is often quite useful. GUIDs are not normally generated in order, so they are of no use in this regard.

3. Size: Less of an issue these days, but a GUID is 16 bytes long. It takes up more space in the table and any indexes that include it.

The globally unique id is actually an integer that just ++'s each time there is a new one. Is this common practice or are GUID's always 16 bytes long?
–
soopriseSep 30 '11 at 15:01

GUIDs are 128 bits (16 bytes) so as to have a sufficiently large namespace. Traditionally they include unique items such as MAC addresses and timestamps so as to give some part of the value a genuinely unique base. I wonder what types your identifiers are and whether they are genuinely GUIDs - normally GUIDs are at least partially random.
–
ConcernedOfTunbridgeWellsSep 30 '11 at 16:24

The ID's are generated by searching all of the ID's, taking the max, and adding 1.
–
soopriseSep 30 '11 at 17:59

Denny - as a moderator and a highly experienced DBA, I'm surprised you didn't add a tiny bit more detail to your answer. Perhaps it would be nice to say why randomly generating a GUID is bad practice in general, or why it doesn't really pose a problem in terms of uniqueness. As it stands, the system wants to know if I should recommend deletion of your post.
–
Max VernonNov 15 '13 at 15:39