I was going to say before I screw it up, but that would have made the title too long.
I've read some forum history and done some searches and can't see that a similar query has come up previously - but if anyone can point me at previous relevant threads then that would be much appreciated. I've looked at some of the design theory threads, but am not really sure whether I've applied some of the good stuff in there effectively - and have put this query here as it's specific to my database rather than a 'general principles' thing.
I am hoping that some kind person/people might be able to pick holes in my planned database revamp as I may not have seen some of the pitfalls - thank you.
Background: I am the first and sole employee of a grant-making trust and set up our (still relatively small) database early on - wish I could have waited, but before my database everything was paper-based and impossible to manage. The data is all on the database, but an exponential growth in number of enquiries/applications over the last year means that it can't be easily manipulated in a way that meets our needs because of my short-sighted (OK, poor owing to inexperience in job) initial design in some areas.
Database:
What it's supposed to do: In brief, the way our organisation works is that we get a phone call or a letter or sometimes an entire application from a charity or community group, decide what to do with it and have to manage the funding contract (regular reviews, grant instalments) if the application is successful.
I think existing data is reasonably normalised (certainly at least equivalent to normalisation in the structure below) so migration shouldn't be a problem, but if anyone spots any normalisation or naming convention issues then please do point them out - I'm an Access amateur, but would like to get it as right as I can (this time). Small letters indicate linked tables, numbers indicate 'footnotes'.
tblProject
ProjID (primary key)
ProjName (a)
ProjDateRecd
Organisation (b)
ProjSource (c)
ProjStatus (d)
ProjResponse (Lookup: Letter, E-mail, No response*) (1)
ProjRespDate
ProjReasonDecline (e)
ProjNote (description)
(a) tblProjEvent
PEvID (primary key)
PEvDate
ProjName
ProjEvent (g)
ProjEvDesc - additional detail - e.g. who involved, topic if not obvious from event field.
ProjEvActWho - who is responsible if future action
ProjEvActDue - due date
ProjEvActComp - checkbox
ProjEvActCompDate
ProjEvNote (NB not to duplicate description - more for 'additional notes arising from completed action' or sensitivities that may need to be considered and kept separate from regular reporting)
(b) tblOrganisation
Details not listed for brevity, but split one field per line of address/detail, org name as primary key - existing table can be migrated as is (2). Includes suppliers, networking orgs. (f)
(c) tblProjSourceLup
How they heard about us - list of sources for speed/consistency of data entry.
(d) tblProjStatusLup
What it says on the tin - where we're at right now with a project. History/status changes recorded in tblProjAction.
(e) tblProjReasonDecline
List of common reasons for speed/consistency of data entry
(f) tblOrgContacts
OrgContact ID (primary key)
Names/personal details of organisational contacts, split one field per item (firstname, lastname, jobtitle, etc).
(g) tblProjEventLup
Letter, Funding contract, review meeting, report, telecon, Board review, etc. May be a future action.
Accounts functions:
Payments in/out linked to organisation with a lookup table for account types (in, out, grant, donation, expenses etc etc). Works fine and can be migrated as is (2)
The current arrangement of relationships has the organisation as the focus - I'm looking to make the project the focus, which seems to make sense as each project is unique - whereas we may get several project applications from the same organisation - and the existing structure makes it impossible to sort these out
.
Footnotes
1) Vast number of applications that fall into the "arrive, get read, get binned" category make this duplication worthwhile - most applications won't make it into the Project Event table. Apart from the "Greetings in the name of the Lord"/419 e-mails, they do all need recording somehow so we can spot any trends and correct misinformation - save hard-working fundraisers wasting their time*.
2) Of course data validation will be necessary, but in most tables we're talking hundreds or thousands of records only, not tens of thousands so should not be that onerous. Especially as I've been the only person using the database, so there are hopefully not too many inconsistencies!
This post is too long already, but if I've been uselessly unclear in explaining anything or anyone wants any more before they want to comment on efficacy of structure please let me know. All feedback appreciated (as I said, I work alone, so really looking for idea-bouncing to check I can see the wood as well as the trees as much as anything - thanks).
*As charity is always a sensitive area and some of you may be involved with voluntary work, an OT comment: 'No response' might seem rude - but if an organisation is -clearly- excluded by the criteria on our website, or that very same person (or a large charity who should be able to manage its information effectively) has previously been told that they are ineligible by reason of their location, size or type then they have had their response already. I could spend all day every day writing 'No' letters - I'd rather be doing something more positive with my time (like checking out/meeting with organisations that are potentially eligible so they get their answer - and in some cases money ASAP). Not R-ing TFM (aka grant guidelines) apparently applies to fundraisers as much as any other profession
I've read some forum history and done some searches and can't see that a similar query has come up previously - but if anyone can point me at previous relevant threads then that would be much appreciated. I've looked at some of the design theory threads, but am not really sure whether I've applied some of the good stuff in there effectively - and have put this query here as it's specific to my database rather than a 'general principles' thing.
I am hoping that some kind person/people might be able to pick holes in my planned database revamp as I may not have seen some of the pitfalls - thank you.
Background: I am the first and sole employee of a grant-making trust and set up our (still relatively small) database early on - wish I could have waited, but before my database everything was paper-based and impossible to manage. The data is all on the database, but an exponential growth in number of enquiries/applications over the last year means that it can't be easily manipulated in a way that meets our needs because of my short-sighted (OK, poor owing to inexperience in job) initial design in some areas.
Database:
What it's supposed to do: In brief, the way our organisation works is that we get a phone call or a letter or sometimes an entire application from a charity or community group, decide what to do with it and have to manage the funding contract (regular reviews, grant instalments) if the application is successful.
I think existing data is reasonably normalised (certainly at least equivalent to normalisation in the structure below) so migration shouldn't be a problem, but if anyone spots any normalisation or naming convention issues then please do point them out - I'm an Access amateur, but would like to get it as right as I can (this time). Small letters indicate linked tables, numbers indicate 'footnotes'.
tblProject
ProjID (primary key)
ProjName (a)
ProjDateRecd
Organisation (b)
ProjSource (c)
ProjStatus (d)
ProjResponse (Lookup: Letter, E-mail, No response*) (1)
ProjRespDate
ProjReasonDecline (e)
ProjNote (description)
(a) tblProjEvent
PEvID (primary key)
PEvDate
ProjName
ProjEvent (g)
ProjEvDesc - additional detail - e.g. who involved, topic if not obvious from event field.
ProjEvActWho - who is responsible if future action
ProjEvActDue - due date
ProjEvActComp - checkbox
ProjEvActCompDate
ProjEvNote (NB not to duplicate description - more for 'additional notes arising from completed action' or sensitivities that may need to be considered and kept separate from regular reporting)
(b) tblOrganisation
Details not listed for brevity, but split one field per line of address/detail, org name as primary key - existing table can be migrated as is (2). Includes suppliers, networking orgs. (f)
(c) tblProjSourceLup
How they heard about us - list of sources for speed/consistency of data entry.
(d) tblProjStatusLup
What it says on the tin - where we're at right now with a project. History/status changes recorded in tblProjAction.
(e) tblProjReasonDecline
List of common reasons for speed/consistency of data entry
(f) tblOrgContacts
OrgContact ID (primary key)
Names/personal details of organisational contacts, split one field per item (firstname, lastname, jobtitle, etc).
(g) tblProjEventLup
Letter, Funding contract, review meeting, report, telecon, Board review, etc. May be a future action.
Accounts functions:
Payments in/out linked to organisation with a lookup table for account types (in, out, grant, donation, expenses etc etc). Works fine and can be migrated as is (2)
The current arrangement of relationships has the organisation as the focus - I'm looking to make the project the focus, which seems to make sense as each project is unique - whereas we may get several project applications from the same organisation - and the existing structure makes it impossible to sort these out
.Footnotes
1) Vast number of applications that fall into the "arrive, get read, get binned" category make this duplication worthwhile - most applications won't make it into the Project Event table. Apart from the "Greetings in the name of the Lord"/419 e-mails, they do all need recording somehow so we can spot any trends and correct misinformation - save hard-working fundraisers wasting their time*.
2) Of course data validation will be necessary, but in most tables we're talking hundreds or thousands of records only, not tens of thousands so should not be that onerous. Especially as I've been the only person using the database, so there are hopefully not too many inconsistencies!
This post is too long already, but if I've been uselessly unclear in explaining anything or anyone wants any more before they want to comment on efficacy of structure please let me know. All feedback appreciated (as I said, I work alone, so really looking for idea-bouncing to check I can see the wood as well as the trees as much as anything - thanks).
*As charity is always a sensitive area and some of you may be involved with voluntary work, an OT comment: 'No response' might seem rude - but if an organisation is -clearly- excluded by the criteria on our website, or that very same person (or a large charity who should be able to manage its information effectively) has previously been told that they are ineligible by reason of their location, size or type then they have had their response already. I could spend all day every day writing 'No' letters - I'd rather be doing something more positive with my time (like checking out/meeting with organisations that are potentially eligible so they get their answer - and in some cases money ASAP). Not R-ing TFM (aka grant guidelines) apparently applies to fundraisers as much as any other profession
