Forum Discussion
Database design
Congratulations on taking the logical first step. Too many people dive in without studying the water and end up swimming with unseen crocodiles.
One of the advantages of SQL Server is that it can handle the security concerns you have whereas Access alone would have a tough go of it.
I think one database is acceptable. You could create multiple schema in that database, one for each site. That would segregate their data, but also mean duplicating tables, views, stored procedures, etc.
An administrator could have permissions on all three to see the aggregate data. I'm not advocating that approach, but it is one to consider.
A strong advantage here is that you can implement permissions within SQL Server so that you can restrict different users ability to see specific rows of data. You could create Roles for each site and assign people at each site to their site's Role, and then give each Role permissions on the pertinent rows of data for their site. At our recent User Group meeting, Anders Ebro gave a presentation that covers these security questions. It's a good introduction.