SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

handling data for within application

handling data for within application

SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1918 Visits: 702
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.


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum