I have a database design issue I'm trying to work out and I thought I'd come to the experts.

I have a table "counts" which does just that - holds counts of certain objects/datasets. The majority of the table is foreign keys describing the components of each sum in the table - type, source, location, etc.

My scenario is, say I have 5 possible types of widgets being counted. If one source only contains 1 widget, do I enter '0' for the others or not even make an entry into the DB and work it out via code when doing statistics/charts?

Is there a standard for "positive feedback" for the lack of a better term? Meaning, with 0's there's less ambiguity vs no entry could mean there's a problem.

I can’t think of a good reason for treating a count of zero as different from any other number.

It is worth noting that SQL has an odd feature/quirk/bug (depending on your point of view) that’s relevant to your question. Given the following query:

SELECT SUM(Quantity) Quantity FROM tbl WHERE Product = 'Widget';

SQL will return a NULL Quantity if there are no Widgets in the table, instead of the mathematically obvious and common-sense answer of zero. (Null is the result decreed by the SQL standard anyway. Check your particular DBMS to see if it actually does this.)

By recording a zero instead of just not having a row you can ensure that such queries are less likely to return a null when you don’t expect one.

mind you it does beg the question why you have a table which contians 'counts' of items already in the db.....
or have I misread you design?

So simple, yet so profound in my case.

I adjusted the perl code feeding the database and everything else just snapped into place.

Thanks

I might have not stated it correctly. The table isn't a count of things in the db, else I'd hopefully be able to use the COUNT function. The counts are fed from different sources via perl to the database. I'm basically writing something to monitor and track I/O for different systems.