0

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

3

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.

  • What is the benefit of staging? Is it because a staging table will be comparably tiny to the rest of the data and be quicker to query/update? – aydow Aug 30 '16 at 23:59
  • The data cleansing process is applied only to data that has just been received. That process is faster and more reliable if it's applied to data in a segregated area. Retrieval processes on warehouse data don't have to have any extra logic to avoid data that has yet to be cleansed. And the structure of the staging table can be identical to that of the data feed, while the structure of the warehouse data can be different: something like a star schema. – Walter Mitty Aug 31 '16 at 12:15
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.

  • I'm trying to avoid this overhead :) – aydow Aug 31 '16 at 0:01
  • Like I said - depends on number of those clients and providers. If it's counted in hundreds of thousands (or rather millions I suppose), then I think You'd still be safe with relation table (that depends how many other tables would be involved in queries about both tables too, tho). If You decide that this solution isn't good for You, I'd start with JoshRagem's proposition (temp tables) - if it's possible of course. Otherwise, just like Walter Mitty wrote, You can live without foreign key, as long as You will remember about implementing relation in some other way. – Marcin S. Aug 31 '16 at 7:25
1

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.

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.