Tag: database design

There is plenty of debate over how to design your database. At SQLServerCentral we have a Stairway Series as well as a few articles that cover design topics. I think it’s important for anyone that builds tables to spend some time learning what others have done and understand the pros and cons of making different choices. It does often become hard to change designs once they are in use, so trying to choose a good entity design early is important.

One of the things I think is important in modeling your particular entity is including a primary key (PK). In my DevOps talk I stress this, as I’d rather most attendees come away thinking a PK is important as their first takeaway from the session. There are exceptions, but they are rare, and I would prefer that most tables just have some PK included from the beginning.

A PK ought to be stable as well, and there are plenty of written words about how to pick the PK for your particular problem domain. Often I have received the advice that natural keys are preferred over surrogate keys, and it is worth the effort to try and identify a suitable column (or set of columns) that will guarantee uniqueness. I think that’s good advice, and it’s also advice I tend to ignore.

There’s an interesting article about keys and the GDPR. The first part is a rather basic description of what PKs are, but the second part talks about keys and some of the rights that data subjects have under the GDPR. I think these are worth considering, especially as it’s likely similar legislation will make its way into other jurisdictions, as already seen in California. The short part of the argument is that the right to be forgotten or to have your data deleted is incompatible with the use of natural keys.

It’s an argument, though I’m not completely sure if I think it would be solid. There are valid reasons to keep some information about a user, and I suspect keeping a list of emails to delete from a database restore as a separate list would be a valid use. Even if the user asked that their information was removed. It would be, but there would also be a need to ensure that the correct data was removed,  hence a list of emails.

The bigger problem for me is that if I needed to redact or alter this key data, which I would likely do in order to keep some integrity in my database, I’d need to alter this data in lots of tables. That makes for a much more complex set of scripts, including ensuring that I am correctly building a map of the new values I would use for a key. It’s much easier to have a surrogate key that doesn’t change and just redact the other information.

I’m sure there are arguments both ways, but as we move towards the era of not only seeing data as valuable, but also as an asset we can’t completely control, I think surrogate keys make more sense now than ever. Let me know if you agree.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

The title says it all: who Likes NULL values in their tables?

I have tended to allow NULLs in quite a few places in my design, often because I view the world as messy and incomplete. I also find that applications are faulty, and might not validate data, might not run long enough without a crash to let a user insert a lot of data. The application might mangle data, or just might not have been updated to support a new column of data. I’ve found that there are times where I accept the messy real world and use NULL to represent unknown values.

Dr. Low notes this as well in a recent post. His view is similar to mine in that he uses NULL values when we don’t know the actual data. This is preferable to some magic value that has to be coded in every application using the database. There are too many chances of mistakes, and definitely the possibility of leakage for these magic values.

As we use more and reporting and aggregation tools, users may inadvertently see strange values exposed. Many of these tools wouldn’t be coded to translate magic values to some agreed upon value, which results in confusion and distraction for clients. The data in our systems becomes used in new and different ways as we start to connect new applications to existing databases. We may also use ETL processes to move information among systems, often to data warehouse or OLAP data stores. Often there are proof of concept prototypes built with self-service tools, such as Power BI, and the logic that was originally coded to translate magic values is lost.

That doesn’t mean that every field should allow NULLs, but that we should consider them in places where the data is useful, but not necessarily mandated or captured in every transactions. If we have valid defaults, use them, but if not, don’t be afraid of NULL. Understand the meaning and implications of allowing NULLs and use them carefully.

I’m curious about if you agree with me. Do you default to NULL values or do you avoid them at all costs? Do you use them judiciously? Give me the reasons why or why not, and if you have examples of where you allow NULLs, let us know.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn.

Across the last few years, I’ve read many articles and listened to quite a few talks that discuss the advantages of NoSQL databases. I’ll admit that I’m often skeptical of the advantages of other datastores overcoming the disadvantages with a relational system, but I try to keep an open mind. I do appreciate that there are some benefits to using another data store in certain situations.

One of the talks I heard recently discussed the fact that in many of these stores, we can add data in a “schemaless” fashion, and it’s stored in a flexible format that allows the developer to quickly capture the data they are using and retrieve it without requiring up front design work to build a particular format.

That had me pondering the question of whether or not here really are schemaless data structures. If a developer  (or whatever SDK or framework they use) looks to persist come data, clearly there is a format of sorts, which means there is a schema. That schema might not be transferred or persisted in the data store, but there is some schema they expect, both on storage and retrieval. Whether this is a JSON, XML, some proprietary structure, or something else, there’s an known structure that the developer uses to work with the data.

Is there really schema-less data? I tend to think no. All of the data we have contains some schema. That schema might vary from row to row, which is often what developers like when building applications. There is, however, a structure. The developer knows it, and must serialize and deserialize the data, or depend on some library like ADO.NET to do so. This often appears to a developer to be a lower barrier to entry. There’s less complexity and often no need to map the objecct-like structure of properties to some relational schema and make decisions on sizes.

That’s not completely true, as the schema of the data still exists and must be persisted in the application. There is code that must handle the various values stored in some hierarchical fashion. If this changes over time, as values are added, the the application must deal with the missing values in older properties or arrays. If items are removed in the application, then would older sets of data just disappear? Perhaps, but the developer must make a decision, which may have implications for users of their application. This doesn’t even deal with the issues of aggregation and reporting, which might force other systems to implement the same schemas and business logic. Those rules and specifications don’t easily transfer from one application to another, especially when different teams or developers are involved.

There’s always a schema, and the rules have to be implemented up front, or later on. Whether you use a RDBMS or a NoSQL store, you are going to be dealing with a schema. The question is do you want to deal with it in a central location or in every application? I lean towards the former, but you might prefer the latter. Neither is wrong, but you should be sure you understand all the advantages and disadvantages of your choice.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.1MB) podcast or subscribe to the feed at iTunes and Libsyn.