I have tried searching for good guidance on this already, but without much luck. Still, apologies in advance if this is duplicated elsewhere.
In a nutshell, we pay external contractors to work on cases for our clients. We already have tables with contractor and client information in our SQL Server database. Going forward we'd like to store accounting and billing info in there too. Pay rates can differ for each client and contractor, but usually each client has a general "default" pay rate that applies to most contractors.
The initial proposal was to create a new table with the following basic design:
clientID- foreign key to client table
contractorID- foreign key to contractor table
basePay- pay rate for this client-contractor combination
...- several more (10+ and likely to grow) columns with supplemental pay rate details
- A unique index (on
contractorID) to optimize lookup and also prevent multiple rows for a given client-contractor combination.
Contractor-specific pay rates would naturally be linked to the relevant contractor (and client). General (default) pay for a client would be stored in a row where
NULL. This is to avoid duplicating the same default pay for all contractors that don't have specific exceptions. For example:
| clientID | contractorID | basePay | ... | =========================================== | 1 | 75 | 12.5 | ... | | 1 | 82 | 15.5 | ... | | 1 | NULL | 10.0 | ... | /* Default pay rate for client 1 cases */
However, one of our senior devs has strong reservations about Option A. Their main argument is that using
NULL in the
contractorID column to mean "this is the default pay rate row" is unintuitive and/or confusing. In other words, it's bad to assign meaning to
Their counter proposal was to duplicate these new pay rate columns in the client table. The data stored there would indicate the default pay for each client, while contractor-specific exceptions would still live in the new table above.
What To Do?
It seems clear both proposals would work just fine, but I have my own reservations about the second. Mainly it seems wrong to store the same type of data (client-contractor pay rate details) in multiple places, not to mention more complex logic to read/write this data. I also don't like duplicating these new columns in both tables, since it would force us to add any future pay rate columns to both tables.
However, I can see my colleague's point about potentially misusing
NULL in this case. At the very least, it's not immediately obvious that rows with a
contractorID contain default pay rates.
It's been far too long since my database programming courses, so I'm not sure what the current best practice for this type of entity relationship is? I'm open to whatever is best long term, and would appreciate any expert guidance, especially with links to additional resources.
Thank you in advance!