The "best practices" method for restricting record access is to use the security model and restrict access based on privilege set. This is done using a Boolean calculation based on a condition associated with the user (such as UserInstance or UserCity) versus a field that's tagged to match in each record. Then, the calculation is:
table::UserCity = currentUserCity
FileMaker will then test each record as it comes across to determine whether the user should have access or not. At its most basic level, records that do not match the criteria will be marked with a "<No Access>" indicator in all fields, which can be annoying to users; hence, it's often best to combine this technique with other techniques such as Script Triggers or instance-specific interface files that are configured only to reveal the correct records to the user. However, the actual privilege set method should not be abandoned, as all other techniques rely on interface-level control and can be bypassed through Export Records, ExecuteSQL and other means.
As far as performance is concerned, I think you'll find the performance impact of record-level access privileges to be small compared to the impact of going from a locally-hosted database (presumably this is peer-to-peer?) to a server-hosted database over the open Internet (WAN). The fundamental reason for this is network bandwidth and latency considerations. There have been several discussions on the forum and in other locations (example: Jon Thatcher's sessions at DevCon) on the impacts of network on database performance and how to optimize a solution to adjust for them. I suggest you do a search for terms like "bandwidth", "WAN", "latency" on the forum and read through the posts.
Mike makes a few good points. Record level access control works. If you really do not need to data from other cities to be accessed but want the server in one place you might find a way to work out having the records from each city in a separate table that are all related in some way to another table. As you add a city you could add a table. You might need to be careful about how the scripts work when doing this. You can easily set the layouts to vary the tables used based on location or login. This would keep the deployment costs down but keep each office only working in its own table of unique records with the ability to look at other city records if they needed to. Without knowing the number of users or records and such it is hard to say how the server speed wll be affected.
The speed is more dependent on the connections and not the actual server.
It sounds like you are afraid of the solution coming to a grinding halt over WAN. If you make a few well thought out decisions this will not be too much of an issue. Get the best internet connections you can afford. Make sure layouts are WAN friendly.
Will it be slower than when you are in the main office? Yes. Will it feel way too slow? Likely not. You may be worried about nothing.
I would suggest maybe testing a WAN connection with your current hosted file. Not very difficult to do and easy to test. You might be happy with what you see.
When we moved from peer-to-peer over to Sever the speed was noticably better locally. I was suprised. I have a number of layouts that are image / field heavy and they load up pretty quick over remote connection from anywhere in the world. Finds take a few seconds longer when you want to access the server from half way around the globe using a mobile wifi unit, but nothing too bad. I was also worried that it would be too slow and that just was not the case.
I would say the connection quality at the server would be the most important. That made the most difference for me.
Hi Mike and Tom,
thanks a lot for your extensive insightful answers. I'll go for "restrict access based on privilege set". From that arises another question:
Cities may be stored in a field "StoredCityID" at the creation of each record. So each table would have a field "StoredCityID". This gets compared to the user currently logged in (CurrentCityID), which is set by the start script as a global field in the Options Table.
SomeTable::StoredCityID = OptionTable::CurrentCityID
In the current solution for each record the username is already stored in all tables. I could fetch the related UserCityID for each user from the table that holds the user privileges and the "UserCityID" and compare that to the global CurrentCityID.
SomeTable::UserID–>UserPrivilegeTable::UserCityID = OptionTable::CurrentCityID
Am I right, assuming that variant A is performing better, because it can directly operate on the stored field "StoredCityID" and not via a relation?
Thanks in advance for your thoughts.
Yes, A will be faster.
Moreover, B will not really work if a user gets relocated to another city over time...
Thanks again for your helpful comments!