Integrity - Database Integrity

Databases are one of the core places that we store data, and if that data doesn't have integrity it can cause problems with our customers, it can cause problems with our users, it can cause us to make poor business decisions, or it can just cause problems with how our databases function. So it's critical that we have a certain amount of integrity with our database.

What a database is

A database is a core way that we store a lot of different data. Let's use an example of a user accessing web servers through the web. What happens is that web server is what's actually handling the communication between a client and our system, but the data is being stored on a separate system that houses a database, a database that has a lot of data within it. So if a user is entering in their username and password, or maybe some sort of information like their company information, maybe the company address, it's going through these web servers but then actually being stored on the database behind the scenes.

A database contains records. They could be records, for instance, about a user. It could be, for instance, me or you. It could have your name, it could have some company information maybe about you, perhaps it has your identification information like your address. That is a record. And those records go into a table. Think of a spreadsheet: a spreadsheet is just a table of data. But a database takes it another step. A database will then store multiple tables and maybe have some sort of correlation between those tables.

A relational database is a type of database. A relational database has correlations between all of the tables. For instance, maybe this table has a bunch of users involved with it, and this table has a bunch of companies in it, and then this table ties those two together. So therefore you could have a user tied to multiple companies, or multiple users tied to a company. And so this is a relational database.

Here's an example of a database table. In this database table we have an ID, it's a record ID, so this is record one, this is record two, this is record five, and each of these records has a company and a first name and a last name associated with it. If we were looking at a relational database, this would not be completely representative of that, because instead we'd probably have some sort of company ID and user ID within here. But this just gives you an example of what a database table could look like.

Why database integrity matters

Let's give some examples of why database integrity is so important. So for instance, let's take somebody's first name. We could take that person's first name and put it into some sort of message that we're going to send them. Let's say it's going to be an email that we're going to send them, and we use the first name at the top of it. Well, if that first name is spelled wrong, then that's obviously going to look really bad on our company. Why are they going to take a look at an email that we can't even spell their name right? And so it's really important that we have their names spelled correctly in here.

Same thing with, let's say, a company name. If we don't have the company name correct, that might not be as big of a deal as a first name, except that we could end up with multiple records of the same company because we spelled it differently every time.

And another example is maybe we have a zip code that's in this table as well. If that zip code is wrong in there, then let's say we're trying to market to a specific zip code: now we're sending email messages out to the wrong zip code.

So it's important that we have this database integrity to be able to pull the correct information and be able to give the correct information. It's important to the users, it's important to the company, it's important all around that this information is correct.

Types of database integrity

There are specific concerns around integrity that we need to watch out for.

For instance, some databases need to have entity integrity. That is, there is some sort of ID; we call this a primary key. So this ID right here is a primary key, and when you have a primary key within a table it needs to be unique. We can't have another record have the same ID, otherwise we don't have integrity with the database, and when it's pulling the data then that can cause problems. So that's entity integrity, when the primary key is not unique.

We also have domain integrity. Domain integrity is concerned about the formatting of each of these columns, to make sure there's consistency. An example might be, this column might have a zip code added to it, and making sure that zip code here in the States is five characters, or five numbers rather. So making sure that each one of these has five numbers to it. Now if one of them ends up having six numbers to it, then we know that that is not a correct zip code. And so we need to make sure that the formats of all the information within here is all going to be the same. That is domain integrity.

Then we have referential integrity. As I mentioned, with a relational database we have multiple tables that could be tied together with another table. For instance, we gave an example of users and companies that are tied together on this table right here. So this record right here requires there to be an entry in here, the user table, and here, the company table. Now if we were to delete one of these records right here, then it creates a problem with this table, and specifically this record right here. And so referential integrity requires that this record exists until its relational records are all deleted.

Then there are custom integrity pieces, or user-defined integrity pieces. That's when we are setting up this database, things that we may be very specific about, things that we defined as being important to us. So there's user-defined integrity. What does that look like? Well, for instance, I don't want another record that has the same person in it, so some sort of integrity piece to make sure that when somebody's maybe creating an account, to make sure that they're not already in the system. And so we're going to take something like perhaps their email and check it against our database to make sure that they're not already in the system. So that's a user-defined example.

Data field validation

One way to make sure we have integrity within our database is to make sure that the data coming into our database has a certain validation already, and so there are data field validations. Let's give an example of maybe a user coming to a page that has a form, and they're filling out that form and it's going into our database. Well, what we can do is we can do some checks, or validation, with the data that they're entering to make sure that it's getting into our database correctly.

An example of this is mandatory fields. When you go to forms, a lot of times the standard is an asterisk; it will identify fields that are mandatory, to make sure we get first name, last name, address, city and so on and so forth.

We also could have some sort of input mask. We commonly see that with things like a state: only two letters are the only thing that you can put in the state. Or an email needs to have an at sign and needs to have a proper domain. Or a phone number needs to have a certain amount of digits, or a zip code can have a certain amount of digits. So some sort of requirement for each one of these fields.

There are things like positive dollar amount, to make sure that people don't put in a negative dollar amount. A good example of that is a negative dollar amount could mean that we're paying them rather than them paying us.

Or date ranges: if there's some sort of date or data ranges, so it could be a date or it could be some sort of number that they're entering in, that it falls between a certain range. Maybe there needs to be some sort of approver on the record before it's entered in.

A maximum record count, so that way if somebody's trying to enter in a lot of records all at once, then that flags as being maybe some sort of concern there, and only allows people to enter in a certain amount of records at any given time.

Or unusual activity triggers, in case somebody's trying to hack our database or trying to input wrong data into it: then there's some sort of monitoring that we can have to make sure that things look standardized, or that the activity that's happening is not unusual.

Validation rules and data types

I already gave some examples of validation rules, but some of it might be the size, of how many characters or numbers can be put in; the type, of can it be numbers, could it be letters, could it be special characters; the format in which it gets entered in; the ranges that can be added into it. And there's also something called check digits.

When it comes to types, we can have integers, decimals or strings. There are more types that are out there; this is just a brief example of the different types. Integer is a whole number, that means there's no decimal at the end of this. Decimal means that it can have a decimal. And then string is some sort of numbers and characters that can be entered in.

Check digits

Some numbers have a built-in check digit in it, so you can enter in the correct information into the database. An ISBN number is a great example of this. ISBN numbers are given out to published books. If you were to have a book that gets published, it would be given an ISBN number, some sort of nine digits with an extra digit as a check digit.

So let's look at how to actually calculate that. If you were to calculate this, you take that first digit in here and multiply it by 10, the second one by nine, the third one by 8, the next one by seven, 6, 5, 4, 3 and two. This is the answer when you multiply that all out. You add all of these up together, which adds up to 303. Now what you're going to do is find a remainder. You're going to find a remainder based off of taking 11 into whatever this number is, which ends up being 27.54-something. So we're going to drop the end off of it, so we see 27 times 11 is 297, and we come up with the remainder, which is six. Well, that's what's going to go up right here.

So now any database can actually calculate for this check digit to verify that all of this is entered in correctly. If one of these numbers is off, then this check digit is not going to calculate quite correctly, and will identify that something has been entered in wrong.

Databases are an important part of our IT infrastructures. We really store a lot of data within databases, data that our customers may use, that maybe our users within our system, our employees, may use. Perhaps it's making business decisions off of the data that's in our databases. And so it's important to have that information correct.