handling data for within application

• JKSQL

  SSCrazy Eights

  Points: 8323

  More actions

  January 7, 2009 at 9:57 am

  #92917

  Currently in our application we have three levels. We are allowed one company (C), many businesses (B) under that company, and then many facilities (F) under a business. There is information that can be configured at any level. Meaning a contact can be at the Comp., bus., or Facility. if it is done at the F level other facilities can not see the contact. If it is Done under the C level everyone can see it.

  It is just not contacts that can be configured at different levels. It is a majority of the system architecture that allows this. All this information is keyed in Guids so there is no way for duplication at any level. Here is a couple catches that I have come up on. Between facilities you can transfer transactions to other facilities. Another one is that say a contact is set up at the C and now they want at the B level to have different contact information. All pre-existing transactions need to be remapped programmatically. This is not a great example but in real life it could be millions of records

  So now for my question. What is the best way to handle this type of information. There are three ways we are thinking of saving this information within the system

  1) Save it at the level which the user sets it as. Meaning user wants a contact at the C,B,F levels. Problem here is when looking for that contact the program will need to scour the DB at each level to see where it should get the information since it will not know which configuration block it is at. I am thinking that will be slow and cumbersome. Also if they decide midstream that a costing block needs to be at a different level then how do we handle historical records. Seemed confusing to me.

  2) No matter where the data is saved we should replicate the information down to the facility. So if they save in the C the same information is replicated to the B and F. now we have duplication within the tables and have to maintain changes to the original. This works as an advantage when they want to turn on something at the business level so historical data is not an issue.

  3) No matter where it is created it is only saved at the facility level. Here we have duplication across F but it would eliminate the other levels.

  This is what we are thinking. I do not think any of these are good or will even work. I am hoping that someone has done something like this before and can lead me in the right direction. If there is something that needs to be defined better or cleared up please ask...

  Thanks for the help.