Around 50 % of the tables in a certain SQL Server database have either zero, one or two rows - is this a sign of a good design?

Question

First off, I'm not a database programmer, nor admin or anybody who is responsible for this area, so I might be wrong in many of the following questions/assumptions.

I've recently encountered a DB schema of about 250 tables. It's a database for an eshop. I've looked into many of these tables and was surprised about how many of them were completely empty or had just one or two rows. So I checked all the tables with:

select count(*)
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1)
where i.rows in (0, 1, 2);

and

select count(*)
from sysobjects t, sysindexes i
where t.xtype = 'U' and i.id = t.id and i.indid in (0,1);

I found out that about 50 % of all the tables are tables with 0, 1, or 2 rows.

I understand that there are some rules for creating databases (normal forms), but to me this just seems too much. For example, wouldn't it be a bit more efficient to store some of this data in one table then in two or more and then having to use joins? The way I imagine this is the database would store data in one table together (close to each other) on the disk, so they could be retrieved fast. But if I use 2 or more tables (therefore I need to use joins when retrieving the data), the data could be stored on different places on the disk, therefore taking more time to retrieve them?

The database is not a big one I suppose, the largest tables have around 1 million rows, I think this could be considered small nowadays. So it might be that on this amount of data, it doesn't make a big difference whether I use joins extensivelly.

I know my question is rather broad. Since I don't have more knowledge about the database design, I can't really be more presice, yet I'd appreciate some of your thoughts on this.

Thank you

Answer 1

How many of the tables are not connected to any other table by a FOREIGN KEY relationship? You can do this by checking out the answer here.

It is possible to have tables with 0 records - if you ran a nuclear power plant for example, you'd want the table catastropic_failure to have 0 records.

Some reference tables could only have 1 or 2 reference codes - maybe the devs thought they'd be added to later?

I would say you're looking at a problem of orphaned tables - you've heard of orphaned records - records with no parents in parent-to-child relationship? These tables been lost and forgotten in the development process.

It was somebody's "bright idea" one day but was subsequently vetoed by senior dev/management - but such was the hothouse and/or firefighting atmosphere in the big open plan room where all the devs worked, that these "bright ideas" were never removed from the code base.

It's also possible that these tables are useful for 1 or 2 clients but not used by the bulk of the system users/clients/whatever?

Another possibility is that you should be careful that the coding style of your establishment (maybe prior to your arrival?) wasn't to use various tables as temporary storage - they are used briefly to store bits and pieces (important nonetheless) of data which aren't properly cleared down until they are used the next time?

What I would do:

1) try and search through your APPLICATION code and see if you can find tables that have no references and 0 records in the code - delete those - TAKE BACKUPS FIRST.

Be especially careful of triggers which may refer to those tables and also stored procedures which reference them as well.

Do you have a test suite for your code - I'm guessing not?

Then do the same with those tables which have 1 and then 2 records. You will probably find a few that can't be deleted without provoking obvious problems. Then there are those which will cause problems which will pop up days/weeks/months/years later. The answer here is it depends on what risks you want to take.

Find the dev and/or managers who've been on the project longest and ask them about this issue - that's probably your best best. By my "rule of thumb" reckoning, you should have, at most, say 10% of your tables in the form you describe. However, unfortunately, it's going to be a difficult mess to clear up.

Best of luck with your project and welcome to the forum! :-)

Answer 2

Is this an application that was developed in house? Or was it a packaged application that the company purchased (and potentially customized)? I'd generally expect that a "database for an eshop" would be purchased rather than being built internally but that's far from a guarantee. If this is a packaged application, this is quite normal.

Generally, packaged applications ship with a bunch of different modules/ options that can be customers can enable and disable depending on the customer's requirements and that the vendor can use for price discrimination. The vendor might, for example, have a local sales tax module to support businesses with a US presence and a VAT module to support businesses in the EU but a particular customer may only need to enable one or the other. And the vendor might have an extra cost module to, say, integrate with an organization's single sign-on provider that they can upsell to large organizations. Generally in this case, it's much easier to create all the tables when the application is installed and let them stay empty until the module is enabled. That makes things like patching and upgrades vastly easier because the vendor doesn't have to worry about testing every script against every possible customer configuration. If you look at a random company's packaged HR/ accounting/ ERP application, you'll see a ton of empty tables. As a side note, one of the things that caused Oracle to implement deferred segment creation so that it didn't have to actually create any structures on disk for a table or an index until you inserted the first row was the large number of empty tables in most eBusiness Suite implementations.

For an in-house application, a bunch of 0 row tables would be odd an an OLTP application. If you are doing a bunch of overnight data loads, you might have permanent tables where you stage the data before inserting it in the "real" tables that only have data in them during the load process. It would be more common for those to be temporary tables in SQL Server which would disappear after the loads but there are situations where it might make sense to have permanent staging tables.

If these are purely lookup tables with a couple of allowed values, it is unlikely to be a performance issue. Wherever the data is stored on disk, a lookup table that fits in a single 8 kb page is very, very likely to basically live in memory rather than requiring the database to read it from disk. And databases are very good at joining. It may require a bit more code to write the joins though that can be mitigated by creating views that do some of the joins.

If the lookup tables have relatively short lookup values that are functionally never going to change, i.e. they're something like

create table address_type_lookup (
  address_type_id tinyint primary key,
  address_type_code varchar(5)
);

insert into address_type_lookup values( 1, 'Home' ); 
insert into address_type_lookup values( 2, 'Work' ); 

then you can have a religious argument about whether it makes sense to change the table to

create table address_type_lookup (
  address_type_code varchar(5) primary key
);

insert into address_type_lookup values( 'Home' ); 
insert into address_type_lookup values( 'Work' ); 

and change the address table to have an address_type_code that references the lookup table rather than an address_type_id. This alternate construction lets you avoid joining to the lookup table without compromising referential integrity. The downside is that it means that you're storing the 4-byte string rather than the 1-byte tinyint in every row of the address table. That's going to mean every row is 3 bytes bigger which means more disk and memory are used and that you'll need to do more I/O to read the data from disk into memory but you can avoid the join, your code may be slightly simpler, and queries are a bit easier so it may be a worthwhile trade-off if the lookup values are smaller or subject to change. You see this sort of thing a lot if you have a lookup table of US state codes that you want to validate against/ provide a drop-down to pick from, you're not concerned that Hawaii is ever going to be something other than HI, and you don't want to join in extra tables every time you want to check what state an address is in.

Answer 3

The other answers make valid points & I've up-voted them. I'd like to address some of your other concerns.

"I understand that there are some rules .. but .. this just seems too much".

The point of normalization is to remove update anomalies. Ideally if one fact in the real world changes then one column of one row in one table must change. If the schema is not sufficiently normalized a change in the real world may require multiple writes to the DB with the risk of inconsistencies creeping in.

And yes, normalization does have costs. Some of these are design-time (because we have to think through the dependencies), some are code-time (more complicated queries to write) and some are run-time (additional query optimization costs). The cost of not normalizing, however, is wrong data and that's a thing we try hard to avoid.

"wouldn't it be a bit more efficient"

To be more efficient you try to improve the amount if X received for a given amount of Y. What are your X and Y?

A design is generally easier to comprehend if one thing performs one function. I'd argue that having specific, small tables with well-defined semantics, and good names, is easier to learn. So this is efficient for comprehension with respect to time.

When writing queries it is again more comprehensible to reference specific entities rather than pull meaning from abstract representation. Which if the following is simpler to understand?

where dbo.Sex.GenerCode = 'M'

OR

where dbo.GenericCode.SubType = 'Gender'
and   dbo.GenericCode.Value   = 'M'

The run-time performance of queries depends largely on the optimizer. If we work with it and allow it to reason succinctly about the SQL we generally get good results. Once again having small specific tables with constraints and indexes specific to their semantics and use-cases will, generally speaking, produce a better, i.e. faster running, results. Consider an example where we have three sets of codes - one for Sex, one for US states and one for countries. The first will have 2 rows (in your usage) the second 50 and the third about 140. These are very different cardinalities so likely to produce different query plans. Given the code 'MA' should the optimizer treat that as "Male", "Massachusetts" or "Morocco"? Again the "queries per second" efficiency is likely to be higher with separate tables. I'm not saying that this combined schema will never work, just that it's putting obstacles in place which need not be there.

I can think of one place where removing or combining the tables would be more efficient. That's in the resource used by the system catalog. This is the internal system area where SQL Server holds the definitions of tables i.e. what columns they have, data types, constraints etc. It is distinct from the user data which the table holds. All modern DBMS have something similar. Removing the cruft will make this smaller. The saving, however, will be infinitesimal, negligible, unmeasurable. It's not worth considering.

"the database would store data in one table together"

SQL Server does not work that way. The unit of IO is an 8kb page of data. Each page belongs to exactly one table. If the table has sufficient rows to need more than one page (unlikely for lookup tables) then having them on adjacent disk sectors can speed large queries as the sectors can be read sequentially as they pass under the disk head. If you use SSD or NVMe this not a concern. Most references to lookup tables will be single-row not large range scans.

Some DBMS recognize that table A and table B are often joined so try to store the corresponding parts adjacently on disk. SQL Server does not.

Many data warehouse designs will deliberately denormalize lookup values specifically to put together on-disk the related values and benefit from sequential scans and no joins. This is a legitimate technique for DW but not for OLTP.

I've jumped around a bit. I hope it makes sense. If I'm not clear please leave a comment and I'll try to explain.