2

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

  • I can post more examples on Monday (don't really have the connection to the DB now), but one example for all is table Sex. Well, I know some people will want 5 different personal pronouns for themselves, but leaving this aside since I come from Eastern Europe where we're far from this, this table will always have just two rows for men and women. So I don't really see much sense in having it as a separate table. – puzzle Oct 5 at 22:08
  • True enough - or a CHECK constraint if it's only ever 2 values. However, there could be method in the madness - OK, it's 2 values for now - and that may be an out-there example, but in the long-term, it's a valid way to do things and the most flexible and powerful. All you have to do is add another record to a table - no searching ENUMs or verifying CHECK constraints - it's done once in the database and forever hold your peace! – Vérace Oct 5 at 23:00
  • Your queries to get the amount of rows are not correct. The count(*) method will always give you a low amount, as one row per NC index + 1 row for a heap / clustered index will be returned. Did you mean to use the rowcnt column? – Randi Vertongen Oct 6 at 8:42
  • Thank you. I found these queries here: stackoverflow.com/a/14163881/10401931 I checked a few of the tables against the output and it didn't seem these queries returned a wrong row count. Anyway, I checked rowcnt column and if I understand, I should use this in the where clause, so it would be select count(*) from sysobjects t, sysindexes i where t.xtype = 'U' and i.id = t.id and i.indid in (0,1) where i.rowcnt in (0, 1, 2); I know you mentioned count(*), but how else should I count the output? My assumption is the data is correct before applying count(*) function on it. – puzzle Oct 6 at 9:38
  • Related: red-gate.com/simple-talk/sql/t-sql-programming/…. In short, do not combine look-up tables. – Michael Green Oct 8 at 2:16
2

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

  • Added a line about being careful about triggers and stored procedures referring to these tables! They can often cause difficult to find bugs when used. Also, 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. – Vérace Oct 5 at 20:58
  • Thank you for your answer. I will try to analyze the foreign key relationships, that's advice I need to follow. As for the rest, I'm not a developer either, I'm a SW Tester, so my means of searching through the code are a bit limited here. I simply feel I want to understand more of the design here and these questions came to me when I worked with the database because of some tests I wanted to execute. But I can always go to our developers and ask them, I think, as you said, this is probably the best course of action. – puzzle Oct 5 at 22:15
  • :) thank you, I guess. I just find it hard to agree with your light criticism of the area of (SW) testing. It's easy to criticise the other side. Most dev I meet do basically only two things: 1) take an already prepared requirement and copy paste it into code knowing just a bit of syntax => hardly any real thinking, 2) call a few already prepared libraries, focusing mostly on what values to send into the constructor and on return values => hardly any real thinking. See, we can go on like this forever, but the point is we can't work without one another. – puzzle Oct 6 at 9:19
  • 1
    I'll delete it - it wasn't a criticism of testers, but rather how they are viewed by devs and management. You only have to look at pay scales for starters. When I was doing it I definitely felt that I was considered at a lesser level than devs by a) devs themselves and b) management (books, training...). And I most certainly do know that some devs are cretins and there are many highly intelligent, enthusiastic testers. I also know that it took me quite a while to get out of testing despite my aptitude and enthusiasm - again, none of this is a reflection on testers - rather the system! – Vérace Oct 6 at 10:02
  • You can leave it here. If you put it like in your last comment, I certainly agree with you, the system is often broken on both sides. I try to code from time to time in my free time to get a better idea what devs do in their jobs, just to understand them a bit better and learn at least some basics of the craft. – puzzle Oct 6 at 10:17
1

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.

  • Thank you for your answer. It's actually a in-house database developed by our team. I'm not completely sure now how much modules there're, this could be a simple explanation as to why there're these empty tables. – puzzle Oct 9 at 17:42
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.