0

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!

4
  • 1
    It wud be nice if you show 2 contractor record and default pay of client records in Option A. Then it will be vety clear.I also have little doubt in option A but I am not sure if I have understood correctly. – KumarHarsh May 12 '20 at 11:02
  • Thanks @KumarHarsh, I have added some sample data. – Jeremy May 13 '20 at 23:33
  • Sorry for late reply. Option A is bad idea.Each client will have only one Default Pay.So you can store it in Client Master itself or create seperate table to Store Client Default pay.Option A is bad idea because of so many reason unless it contain only few records.Later on you will hv problem in when you create composite index in Clientid and contractorid. – KumarHarsh May 20 '20 at 7:00
  • Thanks @Kumar, can you be more specific? Keep in mind our pay info requires several columns, so duplicating all those columns in multiple tables seems pretty bad too. Also note Option A already has the composite index you mentioned, which should help prevent problems (e.g. accidentally adding two default pays). – Jeremy May 20 '20 at 21:33
1

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:

diagram

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.

3
  • Thanks @HandyD, this is a nice compromise, and you highlight its advantages well. Could you please elaborate on the risks to data integrity you mentioned? I'd love to understand those better. I do like this much better than Option B, but it is more complex than Option A. In your experience, would its advantages outweigh its extra complexity? – Jeremy May 13 '20 at 23:44
  • 1
    The risk is that an INSERT or UPDATE that causes a NULL value to be inserted incorrectly implies meaning that it shouldn't. Data integrity was probably a poor choice of words, more like data validity. SQL Server sees NULL as "unknown", rather than as a specific value, so assigning meaning to NULL is going against the normal behaviour of the engine. It is the reason you cannot compare two NULL values with = in SQL Server. Another alternative is to do option A but use -1 instead of NULL. – HandyD May 17 '20 at 23:04
  • Thanks again @HandyD! We considered using a special number to indicate the default pay row. However, this would mean we couldn't use a foreign key constraint, unless we also create the dummy -1 client record as well. – Jeremy May 20 '20 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.