Database design

[JLindquist558]

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.  We will be using a  SQL backend with Access front end set up.  I do have a SQL support person.  But I have some data security/PHI/PII concerns since this is health data. We have Individuals entering information at 6 different sites, but they should not be able to view each other’s data.  The project manager needs to access data from all 6 sites.  Project will continue to add more sites.

Question – What framework should I use?

I have considered one database, with site specific Access front ends linked to views limited to that site.

Or six independent databases, that feed into one for the Project manager.

Or something else.

I was involved with Access programming 15 years ago (ie prior to Software Developer position being created) and have some catching up to do!

Thanks for your help.

[#2]

@JLindquist558 

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.