-2

This question already has an answer here:

We are developing a data model for a marketing database that will import transaction, customer, inventory, etc. files and the directive is ONE process that works for every client. We have been told every client will have different import layouts and different columns that identify a table's primary key.

Our initial idea is to get definitions from the client on what columns make each record unique, store those in a mapping table, and then have lookup tables that translate those primary keys to an internal surrogate keys automatically for every destination table so that every table conforms to an integer primary key no matter how many columns/types are used to make up the real pk.

The first major problem I saw was when/if have to map back to the lookup tables to get data that we did not store in the main data model, but I have ben assured that anything we ever want to query will be duplicated in the lookup and main tables so that should not be a concern.

This kind of flexibility seems like it will cause some serious limitations on:

  1. Technology stack (no way to dynamically map these import files in SSIS, need lot of dynamic SQL or java/c#)

  2. Scalability (based on previous concern and initial testing, this would be difficult to scale without speed concerns)

  3. Complexity (we are already running into some complex code changes when we try to implement all these tables with historical change logging while maintaining mapping to the lookup tables for example)

My question - Is this feasible or is there another obvious solution we are missing?

marked as duplicate by Robert Harvey, Bernard, Eric King, gnat, Mason Wheeler Mar 25 '14 at 3:04

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3

We have been told every client will have different import layouts and different columns that identify a table's primary key.

Dear lord in heaven, DO NOT let this go into production without establishing a clear standard process for giving each record of importance a single, system-wide, actual key. A key that is of the same format for all customers, that you can return their imports with the system key appended to if necessary.

User-specified columns for uniqueness is just asking for data problems, unless your clients are tech-savy--and if they're tech-savy, they can build their own db. Mammoth "map" tables are just a headache waiting to happen; what happens when your client eventually has two "John M Smith" customers, who both have the same phone number and address because a grandfather moved in with the grandson who was named after him?


As for "what to do with fields not added to our main data-set", this is what databases have XML support for. (Or JSON, or an EAV table.) XML is better than JSON in this regard, as most database types with an explicit "XML" data type also have ways to index and slice XML content. (assuming you don't have a database that speaks JSON, that is.)

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