Hi there
I would like some advice, but first I need to give you quite a bit of info, so here goes.
I work for NGO of +-200 users, which (as usual) has ZERO spare cash to invest in a fully fledged Sharepoint / SQL based solution.
Consequently, I am looking to develop a pilot solution in ACCESS to give the management some idea of how an automated system will benefit the organisation. Hopefully they will try to source funding for further development down the track.
In short, we service clients in remote communites, so we have at least one house and an office at each of the 16 communities. This of course means travel and a whole lot more.
So my 'pinky and the brain' solution is to automate the following;
VEHICLES
1A. Maintenance and repairs (including registrations etc) - currently being maintained in a spreadsheet (in a rather hap hazard manner)
1B.We also need a vehicle booking system to manage 'bush passports' - This consists of pre-trip and post-trip checks that need to be carried out.
This is 50+ point checking system that (after checking the vehicle) includes ensuring that safety equipment, plb's, sat phones, drinking water and emergency breakdown equipment is on board..
Currently, this is a paper based system which is open to all kinds of mis-use.
HOUSING
2A. A booking system (per room) so that we dont have too many people in a house at any one time. (This is currently being done on an unwieldly spreadsheet - and managed by one person only)
2B. We also need a more robust method to report lost/damage equipment and repairs required. (Perhaps I can link this to the vehicle post-trip reports)
STOCK
3A. Currently 'stock' items are being captured in a db written in lotus notes (circa 1997) and the data is EXTREMELY dirty because there is no validation or bussiness rules in the single table DB.
Stock items fall into 4 categories.
Consumables : Valued between $1 and $499
Inventory : Valued between $500 and $4999
Assets : Valued from $5000
Special : The value is not important, but its purpose is. (This will be denoted in a seperate field)
Most Assets and Inventory items need to be checked out/in either to individual users, or in the case of items that 'reside' in a remote house, they need to be checked out/in, to a house.
Each of these system could reside in their own DB, but each system requires data that is common to all three.
It may be neccessary to execute some kind of access control which I will implement by checking the users logon name and then comparing that to relevant permissions within a table that I build into the system.
Bear in mind that this is only the beginning of my adventure, and I have no doubt that scope creep has already set in as I have demo'd some concepts to my illustrious leaders. (We are already talking about including some HR functions.)
So now for your advice.
Should I build this as one bohemoth DB (split front end/back end)
OR
Should I (if its even possible) have 4 seperate DBs, (Vehicles, Houses, Stock, Generic Data) that are all tied together via a single front end
(I suspect that this might give a better performance/user experience)
OR
(I know this is NOT the answer, but I put it out there) do I distribute and maintain 3 databases (where I need to maintain the common data in all 3 DBs)
I would like some advice, but first I need to give you quite a bit of info, so here goes.
I work for NGO of +-200 users, which (as usual) has ZERO spare cash to invest in a fully fledged Sharepoint / SQL based solution.
Consequently, I am looking to develop a pilot solution in ACCESS to give the management some idea of how an automated system will benefit the organisation. Hopefully they will try to source funding for further development down the track.
In short, we service clients in remote communites, so we have at least one house and an office at each of the 16 communities. This of course means travel and a whole lot more.
So my 'pinky and the brain' solution is to automate the following;
VEHICLES
1A. Maintenance and repairs (including registrations etc) - currently being maintained in a spreadsheet (in a rather hap hazard manner)
1B.We also need a vehicle booking system to manage 'bush passports' - This consists of pre-trip and post-trip checks that need to be carried out.
This is 50+ point checking system that (after checking the vehicle) includes ensuring that safety equipment, plb's, sat phones, drinking water and emergency breakdown equipment is on board..
Currently, this is a paper based system which is open to all kinds of mis-use.
HOUSING
2A. A booking system (per room) so that we dont have too many people in a house at any one time. (This is currently being done on an unwieldly spreadsheet - and managed by one person only)
2B. We also need a more robust method to report lost/damage equipment and repairs required. (Perhaps I can link this to the vehicle post-trip reports)
STOCK
3A. Currently 'stock' items are being captured in a db written in lotus notes (circa 1997) and the data is EXTREMELY dirty because there is no validation or bussiness rules in the single table DB.
Stock items fall into 4 categories.
Consumables : Valued between $1 and $499
Inventory : Valued between $500 and $4999
Assets : Valued from $5000
Special : The value is not important, but its purpose is. (This will be denoted in a seperate field)
Most Assets and Inventory items need to be checked out/in either to individual users, or in the case of items that 'reside' in a remote house, they need to be checked out/in, to a house.
Each of these system could reside in their own DB, but each system requires data that is common to all three.
It may be neccessary to execute some kind of access control which I will implement by checking the users logon name and then comparing that to relevant permissions within a table that I build into the system.
Bear in mind that this is only the beginning of my adventure, and I have no doubt that scope creep has already set in as I have demo'd some concepts to my illustrious leaders. (We are already talking about including some HR functions.)
So now for your advice.
Should I build this as one bohemoth DB (split front end/back end)
OR
Should I (if its even possible) have 4 seperate DBs, (Vehicles, Houses, Stock, Generic Data) that are all tied together via a single front end
(I suspect that this might give a better performance/user experience)
OR
(I know this is NOT the answer, but I put it out there) do I distribute and maintain 3 databases (where I need to maintain the common data in all 3 DBs)
