Define Field as a Foreign Key?

Question

I am receiving data from an external source in batches that I need to import to my PostgreSQL database. I have 2 main tables: clients and providers. Ideally, they should have a 1:1 relationship, however, this isn't always going to be true with the data I receive. There are cases where a client will be sent in a batch and it's provider counterpart will only be sent in a later batch (or vice versa).

My question is what is the best way to work around this issue? Currently, I have the two tables that are unlinked but have the potential to be if the "keys" are present in both tables but something tells me this isn't the best way to go about things.

Looking forward to hearing your thoughts!

Many thanks

Answer 1

There are two kinds of foreign keys: declared foreign keys and undeclared foreign keys. Most people are familiar with declared foreign keys, but some don't realize that there is such a thing as an undeclared foreign key.

There are several advantages to declaring a foreign key. The big advantage is rejecting invalid data at input time. For interactive data entry, this is very valuable.

In your case, however, it's a detriment. You probably want to store all the data you are given, even if some of it appears to be dirty data. Later, you can go through some kind of cleansing operation where you detect certain kinds of errors, like orphaned references, and flag them for human intervention.

You would use this field in join conditions precisely the way you use foreign keys. This is what is meant by an undeclared foreign key. If there are any orphaned references, they will drop out of the join, and you need to program accordingly.

In a more formal situation, like a data warehouse, incoming data is loaded into a staging table at first, and only added to the warehouse data after cleansing. That's probably overkill in your case.

Answer 2

Another popular idea used in cases like this would be moving relation between clients and providers out of their own tables.

Create third table, that will store just the relations between both existing tables and will allow for n:n relationship. Columns could look like this: client_id, providers_id with both of them being foreign keys to clients and providers. All You need would be adding unique constraint on both columns.

Be aware that this (normalised) approach creates some overhead, as it will require additional JOIN in queries, so it might not be solution for Your data volume.

Answer 3

Based on what you have said, my understanding is that you will get two types of data:

• a row describing the client data
  • client_id, client_info, provider_id
• a row describing the provider data
  • provider_id, provider_info, client_id

But you can't just insert into the structure that you would prefer to use because you don't get all the data at once, and the data has interdependencies that you cannot be sure to be able to resolve in one chunk of data received.

So my suggestion is to use the structure you want--tables with foreign keys--and to put the incomplete data in a staging table(s) with no foreign keys defined. This will let you perform all work needed when you receive the data (reformatting, additional info, etc). When the data can be completed, just drop the completable rows and insert them into the foreign key constrained tables.

If you need to be able to view the incomplete data next to the complete data, use a view to stitch them together.

I think there is no reason to force your self into a complicated solution until you find that the simple one doesn't work.