Results 1 to 5 of 5

Thread: no entry vs '0'

  1. #1
    Join Date
    Jun 2009
    Location
    Hawaii
    Posts
    5

    no entry vs '0'

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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?
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2009
    Location
    Hawaii
    Posts
    5
    Quote Originally Posted by dportas
    I canít think of a good reason for treating a count of zero as different from any other number.
    So simple, yet so profound in my case.

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

    Thanks

  5. #5
    Join Date
    Jun 2009
    Location
    Hawaii
    Posts
    5
    Quote Originally Posted by healdem
    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?
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •