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}:

Diagram of Org. Structure

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... :)

  • You might consider that the organization structure is an input (or some configurable data) of your software – Basile Starynkevitch Sep 14 '14 at 10:10
  • Have you tried finding an alternative to the "where" call? Maybe there is an alternative out there. You could also limit back and forth requests by creating some type of "caching" layer where the data is passed back only at certain intervals. IMO, this question is quite big and could be broken in 2 (one for DB structure and another for DB performance). – Joe Sep 23 '14 at 12:02
  • Could you submit an example of one of your queries? Are you typically looking for information based on SM or on L? Do you have a table of Local(s) with their assigned Sub Management as a foreign key to them? – Jonathan Fite Sep 25 '14 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.