I’m having trouble setting up my program’s database the ‘right’ way. I was hoping you guys could give me suggestions on doing it properly. I am creating a database+client for a non-profit organization that runs all across Canada.

I’ve started to create it already with a web based (PHP with Laravel, HTML, CSS [bootstrap]) frontend and a MySQL based backend.

Currently the organization I am making this database for has 3 main tiers; Main Management ({MM}) > Sub-Management ({SM}) > and Local ({L}). {MM} has multiple {SM}, {SM} has multiple {L}:

Currently I am focusing on the last 2 tiers, {SM} and {L}. {SM} makes sure {L} is running properly and {L} regularly submits different paperwork and reports to {SM}.

I am looking to store some personal information; address, email phone, etc. As well as class/instruction results (marks included). Some inventory management (who has what, very simple). Some positional information (who has what job). And some Attendance info (Who did what, where, for how long).

Actual question:

Currently, I am assigning every {L} an id and using that as a foreign key in all the inventory/personnel/positional tables, but it’s getting ridiculous to do the ‘where’ call everywhere in my queries; tough to keep track of when doing more complex queries.
I was wondering if it would be better to separate each {L} into it’s own database and access each depending on which user is logged in? I’m not sure what would be best or what is accepted as best practice.

Edit: Sorry, I thought about this after… I also (probably) want to be able to allow different members of different {L}s to communicate or transfer member/information between {L}s. Would this be more difficult if they were split up? Maybe I should keep everything together in one database? Yeah. I’m just lost and confused… 🙂