Is it bad to use NULL in foreign key column to mean something (e.g. this is the default data)? I have tried searching for good guidance on this already, but without much luck. Still, apologies in advance if this is duplicated elsewhere.

The Problem

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.

Option A

The initial proposal was to create a new table with the following basic design:

clientContractorPay


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 clientID and 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 contractorID is 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 */


Option B

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 NULL values.

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 NULL 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!
There is a third design option which is to separate your pay rates data into a separate table with no client or contractor information. This table contains every unique pay rate across all clients, contractors etc.

You then create a linking table to link clients, contractors and pay rates, plus add a column to your client table that references the ID of the default pay rate for that client.

This is a rough diagram of what I mean:



This should avoid the negatives of your previous options- you're not giving NULL value rows meaning (not that this is inherently wrong, but there are risks to data integrity by using NULL to infer a relationship to other data), and you're also not storing multiple copies of the same data or blurring the entities in your data model together.

This design also means you can reuse existing pay rates across multiple clients/contractors without duplicating data.