Forum Discussion
JLindquist558
Mar 16, 2022Copper Contributor
Database design
Management has decided not to fill the vacated Software Developer position. We need a project tracking/data collection database and I (the statistician on the project) have been assigned this task. ...
George_Hepworth
Mar 16, 2022Silver Contributor
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.