Sql-server – 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

database-designperformancesql server

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

Best Answer

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! :-)

Related Question