Text Storage and Database Design Optimization in a SQL Server Database

Question

Given a database with a number of columns (c), where some of these columns contain text that is one of multiple values due to a set CONSTRAINT on the columns, what is the most efficient way to structure this database? Also considering the number of rows (r) the database may grow to in size.

Example: given a table with three columns, one being an auto incrementing integer ID column, another being a Fruit column where acceptable fruits are 'Apple','Banana','Orange', and 'Strawberry', along with the final column being Color with 'Red', 'Orange', 'Green', and 'Yellow' as acceptable values. Let us assume the Fruit and Color column have a data type of varchar(50). Which of the following would be a good practice and suitable database design?

1. Should each value be directly represented in the table as follows given the previous example. Would it be fair to say that after a large number of rows querying such a table would be very inefficient due to the large storage and computation requirements required on the text? +-----+--------+--------+ | ID | Fruit | Color | +-----+--------+--------+ | 1 | Banana | Yellow | | 2 | Apple | Green | | ... | ... | ... | | 500 | Orange | Orange | +-----+--------+--------+

   OR

2. For each column that has one of multiple values applicable to it, should that be represented in another table with integer values, and then joined together with an INNER JOIN? Followed by an integer representation in the main table? Does this depend on the length of the text values being stored in that field? For the given example, we would then have three tables. +----+------------+ | ID | FruitName | +----+------------+ | 1 | Apple | | 2 | Banana | | 3 | Orange | | 4 | Strawberry | +----+------------+ +----+------------+ | ID | FruitColor | +----+------------+ | 1 | Red | | 2 | Green | | 3 | Orange | | 4 | Yellow | +----+------------+

   Then followed by: +-----+-------+-------+ | ID | Fruit | Color | +-----+-------+-------+ | 1 | 2 | 4 | | 2 | 1 | 2 | | ... | ... | ... | | 500 | 3 | 3 | +-----+-------+-------+

Is there any mathematical 'rule-of-thumb' when designing a database considering the number of rows and the type of data to be stored in each column? Or should the second schema be followed in general.

Answer 1

If all apples are green and all bananas are yellow a Fruit table (ID int, Name varchar(50), colour varchar(50)) would be appropriate, with your data table having a foreign key to it.

If you have yellow apples and orange bananas (yea for genetic engineering!), but only certain combinations are permitted you will need FruitRainbow(ID int, FruitID, ColourID) with the latter two as FKs to your option 2 tables above and your data table having a FK to FruitRainbow.

If any fruit can occur in any colour, and you don't want to limit those combinations in advance, your option 2 is fine.

If your query is really about resource optimisation rather than relational integrity then you'll have to decide what you want to gain and what you're willing to trade to get it. By using integer FKs instead of natural name you get a smaller disk footprint at the cost of runtime load. There are no free lunches. Pick your problem and solve it in the knowledge of the compromises it will entail.

Answer 2

The second approach is much more normalised and what you would expect to see in an OLTP application database. The first approach is more of a de-normalised approach that you would likely see in a data warehouse for reporting purposes.

The first approach would probably be faster as the less joins you have in a query the quicker it generally is but this can be fixed with indexes that work much better with integers. Also you would be duplicating a lot of data and increasing the size of your database with the first approach.

Answer 3

There are multiple things you should consider before choosing either way. First, let me just clarify - both ways have pros and cons, and your choice should be based on your specific scenario. That's not math, but this are the things you should think about.

Storage Considirations

Obviously, storing the values you mentioned will probably be more expensive, storage wise, than storing ints. Depending on the volume of your data, that might be something you should consider. Of course that's not only the row data we're talking about, but that includes any index in the table in which those columns are key or included columns.

Another part of this consideration is how much duplicate data you'll end up with. if there's almost no duplicate data - then storing it in the same table won't make such difference.

In VLDB's, duplicating data not only mean bigger table and more disk space usage - it also mean less pages of this table can fit in memory (the buffer pool), which cause more slow disk I/O.

Indexing

In that specific example, creating non-clustered index on int column or on varchar column will give you the same result. BUT, sometimes that's not so simple. Let's say the value isn't just short string like "Apple" or "Orange", but long string. For example, browser user-agent string. In that case, the string can be long and of course - it may be not the only column you want to put as key column in the non-clustered index. Since the key columns of non clustered indexes at SQL Server are limited to 900 bytes, in this scenario different table would be better solution (and just storing the ID in the index).

Range Scans

In some scenarios, you want to be able to use the range-scan options and therefore save the data in different table and use a foreign key.

Join

When using your second approach, you might end up having to join 10 table just to make simple report. It's not fun to write, not fun to read - and might complicate things if the tables are big.

Locking

When using the second approach, in busy systems, you should be aware that there will be locks placed on all the tables. And if your "Colors" table used in FK relationship with more than one table, there might be implications on you. So you should be aware that by not duplicating the data, you create a single table which may be very busy and very locked - and everything you do with it later will affect all those tables.

Data Inserts

You should make sure the DAL in your application handle FK inserts well. I've seen Data Access Layers that handle this in very bad way (multiple unnecessary round-trips) that cause a performance problem.

This is the main things i can think about. I guess that there are some more stuff you should be aware of. But again, that's not about "math". that's about analyzing your specific scenario and finding the best solution. And of course, as long as your scenario is more complicated (big table, high insert rates, etc) - then it's more important choosing the best solution.