I have two applications that have clients, the clients on application A may have different names than the application B. I'm receiving an Excel with data from application B that comes with the client name on one of the columns. This Excel can have hundreds of thousands of lines...
I need to check if the client B exists on A, and if not, i have to create it. Now, on B were identified several clients that should represent the same client on A (I can´t change B, if I could I would remove "duplicates") but not always the name that client B has, should be the same in A. With this in mind, there's Excel to map Client A to client B (even if client A is not in A).
Example:
| Client A Name | Client B Name 1 | Client B Name 2 | Etc
| Client 1 | Client asd | |
| Client 2 | | |
| Client 3 | Client fgh | Client 123 |
This mapping is supposed to grow, I would say it is not supposed to changed the existing data, but I can never be sure, and so far it has 6234 lines, 12095 cells with data.
I would like to know the best way to deal with this amount of data in order to process the client.
Ideas i have:
1) Load the Excel into a BD table
Good Things:
- BD are the best way to save data (right?)
- Easy way to load the saved data with one select
- Easy to implement CRUD
Bad Things:
- My company has very bureaucratic stuff... to install in QA and PRD i have to go through 2 teams and sometimes wait more than a week per environment and since this development can still change its requirements and business logic i'm trying to escape this...
- No matter what gui i make people will always use excel to change the mapping and they prefer to keep it that way...
2) Load the mapping to memory everytime i need to run this process
Good things:
- No connections to the BD asking for the data and to save, so no need for CRUD
- One less layer involved
Bad Things:
- Slower
- not dry, since I would repeat a somewhat unnecessary load process every run
3) Load the data from the excel to pc once and everytime the excel changes
- Not sure how this would work out... would it be like creating a local txt/csv file?
I saw some examples like:
JSON: Best way to store data locally in .NET (C#) LocalDB: What is the best way to store data in c# application
XML/Binary serialization
Others?
Notes
For now it is only planned to load the program one time per week, but this can change to once per day, so i would say option 2 is out.
I intend on having the mapping data in c# dictionaries so search can be faster. My idea would be to a a first dictionary for every letter in the alphabet and the insert another dictionary for each client map.
Example
dictionary1
| Key | Value |
| A | dictionaryA |
| B | dictionaryB |
etc
On each dictionary[LETTER] (depending of the first letter of the client B name)
| Key | Value |
| Client asd | Client 1 |
| Client fgh | Client 3 |
| Client 123 | Client 3 |
