CODEWITHSUNDEEP
sqldatabase-design
Matthew Jones
Matthew Jones

Reputation: 26190

SQL Design - Accounting for unknown values

Say I have a table called HoursCharged

ChrgNum(varchar(10))   
CategoryID(uniqueidentifier)   
Month(datetime)   
Hours(int)

CategoryID is a foreign key reference to another table in my database, which is just a name/ID pairing. ChrgNum is guaranteed to be unique outside of this database, and so I only check to see if it exists in my db already.

You should also know that this tool support several different groups from one database (hence the globally unique CategoryID, since different groups may potentially name the Categories the same thing, I needed to differentiate them).

This table is populated from a CSV file. The idea is that every combination of ChrgNum, CategoryID, and Month is going to be unique. The report that is being run to create the import file can only see a certain range (i.e. a year). Therefore the algorithm looks something like this:

IF (ChrgNum exists in database, CategoryID exists in database, 
    combo of ChrgNum/CategoryID/Month DOES NOT exist in table HoursBurned)
THEN add a new row for this entry
ELSE IF (ChrgNum exists in database, CategoryID exists in database,
    combo of ChrgNum/CategoryID/Month DOES exist in table HoursBurned)
THEN update the existing row with the new Hours value.

This is all fine and good, except now my boss wants me to account for hours charged, in a particular month, for a known ChrgNum and an unknown Category.

My question is, how do I account for this? If I simply insert a NULL CategoryID, what happens if a totally seperate group charges hours to the same number and category? My other idea was to create a new table for the unknown Categories, but if I do this, and the first import has two unknown categories while the next one has one of the two again (which can happen) what do I do?

My head has been swirling around this for hours. Any help is appreciated!

Upvotes: 2

Views: 2690

Answers (2)

APC
APC

Reputation: 146239

Your boss presented you with this problem, so why not ask them? Because this really sounds like a business problem. If you have groups which are reporting on categories you don't know about then surely you should be attempting to synchronize your database with the systems which feed it?

Otherwise, what is wrong with having a single "Unknown" category? You are being asked to track the hours assigned to categories you don't currently track.

Upvotes: 2

edsoverflow
edsoverflow

Reputation: 591

It seems to me like you should be adding the unrecognized categories to the existing categories table on the fly. If the problem is then distinguishing between categories with the same name from different groups, don't you already have that problem now?

Upvotes: 0

Related Questions