Is this a standard way to design a database?

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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:

select *
  from Foo
 where FooID = 12345

vs

select *
  from Foo
 where FooID = convert (uniqueidentifier, '5E64E653-35F0-4803-B459-F5F59C701F22')

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.

Answer 4

I won't say that it's normal, but it isn't abnormal either to set things up this way.