Why does the relational model for a database matter?

Question

I am approaching a project where I'll be having to implement a database with my boss; we're a very small start up so the work environment is deeply personal.

He had given me one of the company databases before and it completely went against what I was taught (and read about) in school for RDBMS. For example, there are entire databases here that consist of one table (per independent database). One of those tables is 20+ columns long and for context, here are some of the column names from one table:

lngStoreID | vrStoreName | lngCompanyID | vrCompanyName | lngProductID | vrProductName

The point being is that where he should have individual tables that hold the entity data (name, size, date purchased, etc.) he shoves it all in one large table per database.

I want to improve this design, but I am not sure why a properly-normalized and segmented data model would actually improve this product. While I am familiar with database design from college and I understand how to do it, I am unsure why this actually improves databases.

Why does a good relational schema improve a database?

Answer 1

The performance argument is usually the one which is most intuitive. You especially want to point out how it will be difficult to add good indexes in an incorrectly normalized database (note: there are edge-cases where denormalization can in fact improve performance, but when you are both inexperienced with relational databases you will likely not easily see these cases).

Another is the storage size argument. A denormalized table with lots of redundancies will require far more storage. This also plays into the performance aspect: the more data you have, the slower your queries will be.

There is also an argument which is a bit harder to understand, but is in fact more important because you can't solve it by throwing more hardware at it. That's the data consistency problem. A properly normalized database will take care by itself that a product with a specific ID always has the same name. But in a denormalized database such inconsistencies are possible, so special care needs to be taken when it comes to avoiding inconsistencies, which will take up programming time to get right and will still cause bugs which will cost you in customer satisfaction.

Answer 2

I'll be having to implement a database with my boss ...

Using dedicated Database Management software might be considerably easier (sorry; couldn't resist).

lngStoreID | vrStoreName | lngCompanyID | vrCompanyName | lngProductID | vrProductName

If this database only cares about "logging" which product was sold where, when and by whom, then you might be able to stretch the Definition of "OK database" far enough to cover it. If this data is being used for anything else, then it's really pretty poor.

But ...

Does the application/ queries using this data respond poorly/slowly? If not, then there's no real Problem to solve. Sure, it looks and feels ugly, but if it works then you're not going to get any "points" for suggesting it "could" be better.

If you can find definite symptoms (i.e. problems) that look like they're caused by poor data modelling, then prototype a better solution. Take a copy of one of these "databases", normalise the data and see if your solution runs any better. If it's considerably better (and I would fully expect that any update operations on this data would be massively improved) then go back to your boss and show them the improvement.

It's perfectly possible to recreate his "single-table view" of the data with .. well .. Views.

Answer 3

Why does a good relational schema improve a database?

The answer is: it doesn't always improve a database. You should be aware that the what you were likely taught is called Third Normal Form.

Other forms are valid in some situations, which is key to answering your question. Your example looks like First Normal Form, if that helps you feel better about its current state.

3NF rules establish relationships among data that "improve" a database:

1. Prevent invalid data from entering your system (if a relationship is 1-to-1 it forces an error despite the code written on top of it). If your data is consistent in the database, it is less likely to result in inconsistencies outside your database.

2. It provides a way to validate code (e.g. a many-to-one relationship is a signal to restrict an object's properties/behaviors). When writing code to use the database, sometimes programmers notice the data structure as an indicator of how their code should work. Or they can provide useful feedback if the database doesn't match their code. (This is more like wishful thinking, unfortunately.)

3. Provide rules that can significantly help you reduce mistakes when building a database, so that you are not building it based on arbitrary requirements that might come at any time during the life of a database. Instead, you are systematically evaluating the information to achieve specific goals.

4. Proper database structures lead to improved performance by connecting data in ways that minimize data storage, minimize storage calls to retrieve data, maximize in-memory resources and/or minimize data sorting/manipulation for the particular dataset you have, compared to the query you are executing against it. But "proper" structure depends on the amount of data, nature of the data, type of query, system resources, etc. By normalizing you may make performance worse (i.e. if you load all of the data as 1 table - joining can slow down a query). Transaction processing (OLTP) vs business intelligence (data warehouse) are very different.

In a small company with small data sets, you might find that there is nothing wrong with the way it is now. Except, if you grow, it will be a pain to "fix" later, because as the table gets large, the systems that use it will likely go slower.

Usually you will want to emphasize fast transactions as a company grows. However, if you spend time on this project now instead of other things that the company may need more urgently, you may never have that problem because your company never really grows. That is the "pre-optimization challenge" - where to spend your precious time right now.

Good luck!

Answer 4

There a multiple reasons why using one large "god table" is bad. I'll try and illustrate the problems with a made up example database. Let's assume you are trying to model sporting events. We will say you want to model games and the teams playing in those games. A design with multiple tables might look like this (this is very simplistic on purpose so don't get caught up in places where more normalization could be applied):

Teams
Id | Name | HomeCity

Games
Id | StartsAt | HomeTeamId | AwayTeamId | Location

and a single table database would look like this

TeamsAndGames
Id | TeamName | TeamHomeCity | GameStartsAt | GameHomeTeamId | GameAwayTeamId | Location

First, let's look at making indices on those tables. If I needed an index on the home city for a team, I could add it to the Teams table or the TeamsAndGames table pretty easily. Remember that whenever you create an index, that needs to be stored on disk somewhere and updated as rows are added to the table. In the case of the Teams table this is pretty straightforward. I put in a new team, the database updates the index. But what about for TeamsAndGames? Well, the same applies from the Teams example. I add a team, the index gets updated. But it also happens when I add a game! Even though that field will be null for a game, the index still has to be updated and stored on disk for that game anyway. For one index, this doesn't sound too bad. But when you need many indices for the multiple entities crammed into this table, you waste a lot of space storing the indices and a lot of processor time updating them for things where they don't apply.

Second, data consistency. In the case of using two separate tables, I can use foreign keys from the Games table to the Teams table to define which teams are playing in a game. And assuming I make the HomeTeamId and AwayTeamId columns not nullable, the database will ensure that every game I put in has 2 teams and that those teams exist in my database. But what about the single table scenario? Well, since there are multiple entities in this table, those columns should be nullable (you could make them not nullable and shove garbage data in there, but that is just a horrible idea). If those columns are nullable, the database can no longer guarantee that when you insert a game that it has two teams.

But what if you decide to just go for it anyway? You set up the foreign keys such that those fields point back to another entity in the same table. But now the database will just make sure that those entities exist in the table, not that they are the correct type. You could very easily set GameHomeTeamId to the ID of another game and the database won't complain at all. If you tried that in the multiple table scenario, the database would throw a fit.

You could try to mitigate these issues by saying "well, we will just make sure we never do that in code". If you are confident in your ability to write bug free code the first time and in your ability to take into account every strange combination of things a user might try, go right ahead. I personally am not confident in my ability to do either of those things, so I'll let the database give me an extra safety net.

(This gets even worse if your design is one where you copy all relevant data between rows instead of using foreign keys. Any spelling / other data inconsistencies will be hard to resolve. How can you tell if "Jon" is a misspelling of "John" or if it was intentional (because they are two separate people)?)

Third, almost every column needs to be nullable or must be filled with either copied or garbage data. A game doesn't need a TeamName or TeamHomeCity. So either every game needs some kind of placeholder in there or it needs to be nullable. And if it is nullable, the database will happily take a game with no TeamName. It will also take a team with no name, even if your business logic says that should never happen.

There are a handful of other reasons why you would want separate tables (including preserving developer sanity). There are even a few reasons why a larger table might be better (denormalization sometimes improves performance). Those scenarios are few and far between (and usually best handled when you have performance metrics to show that that is really the problem, not a missing index or something else).

Finally, develop something that will be easy to maintain. Just because it "works" doesn't mean it's OK. Trying to maintain god tables (like god classes) is a nightmare. You are just setting yourself up for pain later.

Answer 5

Quote of the day: "Theory and practice should be the same... in theory"

Denormalized table

Your unique hold-it-all table contains redundant data has one advantage: it makes reporting on its lines very simple to code and fast to execute because you don't have to do any joins. But this at a high cost:

• It holds redundant copies of relations (e.g. IngCompanyID and vrCompanyName). Updating master data might require to update many more lines than in a normalized schema.
• It mixes everything. You can't ensure an easy access control at database level, e.g. ensuring that user A can update only company info, and user B only product info.
• You can't ensure consistency rules at database level (e.g. primary key to enforce that there's only one company name for a company id).
• You don't fully benefit from the DB optimizer who could identify optimal access strategies for a complex query, taking advantage of size of normalized tables and statistics of several indexes. This might quickly offset the limited benefit of avoiding joins.

Normalized table

The disadvantages above are advantages for the normalized schema. Of course, the queries might be a little more complex to write.

In short, the normalized schema expresses much better the structure and relations between your data . I'll be provocative and say it's the same kind of difference than between the discipline required to use a set of ordered office drawers and the ease of use of a trash bin.

Answer 6

I think there are at least two parts to your question:

1. Why shouldn't entities of different types be stored in the same table?

The most important answers here are code readability and speed. A SELECT name FROM companies WHERE id = ? is just so much more readable than a SELECT companyName FROM masterTable WHERE companyId = ? and you are less likely to accidentially query nonsense (e.g. SELECT companyName FROM masterTable WHERE employeeId = ? wouldn't be possible when companies and employees are stored in different tables). As for speed, data from a database table is retrieved either by reading the full table sequentially, or by reading from an index. Both are faster if the table/index contains less data, and that's the case if the data is stored in different tables (and you only need to read one of the tables/indices).

2. Why should entities of a single type be split into sub-entities that are stored in different tables?

Here, the reason is mostly to prevent data inconsistencies. With the single table approach, for an order management system you might store the customer name, customer address and product ID of the product the customer ordered as a single entity. If a customer ordered multiple products, you'd have multiple instances of the name and address of the customer in your database. In the best case, you just got duplicate data in your database, which may slow it down a bit. But a worse case is that someone (or some code) made a mistake when the data was entered so that a companies ends up with different addresses in your database. This alone is bad enough. But if you were to query a company's address based on its name (e.g. SELECT companyAddress FROM orders WHERE companyName = ? LIMIT 1) you'd just arbitrarily get one of the two addresses returned and wouldn't even realize that there was an inconsistency. But each time you run the query you may actually get a different address, depending on how your query is resolved internally by the DBMS. This will likely break your application somewhere else, and the root cause of that breakage will be very hard to find.

With the multi-table approach, you'd realize that there is a functional dependency from company name to company address (if a company can have only one address), you'd store the (companyName, companyAddress) tuple in one table (e.g. company), and the (productId, companyName) tuple in an another table (e.g. order). A UNIQUE constraint on the company table could then enforce that each company only has a single address in your database so that no inconsistency for company addresses could ever arise.

Note: in practice, for performance reasons you'd probably generate a unique companyId for each company and use it as a foreign key instead of using the companyName directly. But the general approach stays the same.

Answer 7

TL;DR - They're designing the database based on how they were taught when they were in school.

I could have written this question 10 years ago. It took me some time to understand why my predecessors designed their databases the way they did. You're working with someone that either:

1. Gained most of their database design skills using Excel as a database or
2. They're using the best practices from when they got out of school.

I don't suspect it's #1 since you actually have ID numbers in your table, so I'll assume #2.

After I got out of school, I was working for a shop that used an AS/400 (aka IBM i). I found some odd things in the way they designed their databases, and began advocating we make changes to follow how I was taught how to design databases. (I was dumb back then)

It took a patient older programmer to explain to me why things were done that way. They hadn't changed the schema because it would have caused programs that were older than me to break. Literally, the source code for one program had a creation date of the year before I was born. On the system we were working on, their programs had to implement all the logic and operations that your database's query planner handles for you. (You can see that by running EXPLAIN on one of your queries)

He was up-to-date on techniques I was trying to implement, but keeping the system running was more important than making changes "because it went against what I was taught". Every new project either of us started made best use of the relational model that we were able to. Unfortunately, other programmers/consultants from that time still designed their databases as if they were working with the former constraints of that system.

---

Some examples of what I encountered that didn't fit the relational model:

• Dates were stored as Julian day numbers which required a join to a date table to get the actual date.
• Denormalized tables with sequential columns of the same type (e.g. code1,code2, ..., code20)
• NxM length CHAR columns representing an array of N strings of length M.

The reasons that I was given for those design decisions were all based on the constraints of the system when the database was first designed.

Dates - I was told it took more processing time to use date functions (which month or day or weekday) to process a date than it did to create a table of every possible date with all of that information.

Sequential columns of the same type - The programming environment they were in allowed a program to create an array variable over part of the row. And it was an easier way to reduce the number of read operations.

NxM Length CHAR columns - It was easier to shove configuration values into a one column to reduce file read operations.

A poorly conceived example in C equivalent to reflect the programming environment they had:

#define COURSE_LENGTH 4
#define NUM_COURSES 4
#define PERIOD_LENGTH 2

struct mytable {
    int id;
    char periodNames[NUM_COURSES * PERIOD_LENGTH];  // NxM CHAR Column
    char course1[COURSE_LENGTH];
    char course2[COURSE_LENGTH];
    char course3[COURSE_LENGTH];
    char course4[COURSE_LENGTH];
};

...

// Example row
struct mytable row = {.id= 1, .periodNames="HRP1P2P8", .course1="MATH", .course2="ENGL", .course3 = "SCI ", .course4 = "READ"};

char *courses; // Pointer used to access the sequential columns
courses = (char *)&row.course1;


for(int i = 0; i < NUM_COURSES; i++) {

    printf("%d: %.*s -> %.*s\n",i+1, PERIOD_LENGTH, &row.periodNames[PERIOD_LENGTH * i], COURSE_LENGTH,&courses[COURSE_LENGTH*i]);
}

Outputs

1: HR -> MATH
2: P1 -> ENGL
3: P2 -> SCI
4: P8 -> READ

According to what I was told, some of this was considered best practice at the time.