Question

Topic: Other

Database Dilemma

Posted by Anonymous on 2/24/2010 at 4:22 PM ET 500 Points
Hello Everyone

I'm finishing a small contract compiling a database for a national organisation of schools, organisations and individuals interested in outdoor learning.

The list was compiled through the goodwill of people being willing to circulate an email that was sent out containing a brief form to complete and return.

What I'm looking for is suggestions as to ways of continuing to raise the profile of this database and enable interested people to get their names added, with the minimum of work, effect and cost to the organisation.

Please do ask questions - I'm happy to clarify matters.

Thanks
Juliet
To continue reading this question and the solution, sign up ... it's free!
SUBSCRIBE
Or, SIGN IN

RESPONSES

  • Posted by michael on 2/24/2010 at 5:16 PM Member
    Juliet,

    Seems like you've done a lot if you're at the point where you can finish...without closing.

    I only get confused with "outdoor learning" because some would consider that classroom related functions and others would consider that for motorcoach trips to historical places.

    Michael
  • Posted by michael on 2/24/2010 at 6:51 PM Accepted
    Juliet,

    I'm assuming then that the goal is to remain local...not
    expand outside the boarders of Scotland?

    Clearly, having a form on the website it key. Driving people to the site...maybe a different problem.

    Ask the museums if the organization can be included in mailings to their "friends of the museum" list.

    I initially thought motorcoach, but they tend to be from out of town/country.

    Michael
  • Posted by Jay Hamilton-Roth on 2/25/2010 at 9:50 AM Accepted
    Make the benefit for someone to sign-up clear.

    What information will people get? How often? Will it be targeted to specific needs? Will it be information or sales pitches? Can they see examples of previous information online?
  • Posted by steven.alker on 2/25/2010 at 9:53 AM Accepted
    Dear Juliet

    An ode to the design of databases and Grecian urns for voluntary organisations.

    The key to this question is to answer it quickly but completely so that you can get your boots back on and get outside. Databases tend to corrupt your kind of genius!

    If you ask yourself about the eventual purpose of the data held on the database (Address lists, looking up people, mailings, appending relevant information etc) then you can avoid the usual dead ends in database design which become expensive mistakes some way down the road.

    That will lead you to a suitable database structure – don’t worry if the current data is not in an ideal form, it’s easier to change it early on rather than later. This will give you a sort of hierarchy which is easy to understand and translates well between different software packages so when people inevitably start to say “Use Salesforce.com or MySQL or Access or Sugar CRM or whatever” you can skip the branding bullshit and cut to the detail of what your members want to do. Oh and who in the organisation, if anyone can support it. Do at all costs try to avoid a “Kevin” (With apologies to real Kevin’s – they are great!) who is very, very keen on databases as he’ll produce something which will grind to a halt in 6 months time and then leave to work in something less rewarding.

    The basic database entry is usually a record – from what you have said, this sounds like a name / address / contact details sort of record. If that’s the case, then the record will be structured out of discrete fields. Remember I said that you should avoid dead-ends – well, an example of this is the name field. You would be astonished how many databases have a field called “Name and Title” which contains something like “Dr Peter J Cunningham, Lecturer” which is great until you want to find him by his last name, or make a list of all members who are lecturers or merge a letter with the formal Dear Dr Cunningham or the informal Dear Peter. A better structure from the start would have been to have several fields for this information instead of one such as “Title” “First name” , ”Middle Name” , “Last Name” , “Job Title” and so on. That might seem like an explanation of the bleeding obvious, but you should apply this logic to any field of data you want to store in a record.

    The next basic thing is the kind of data is contained in a field. That might appear to be equally obvious as its all data init? Well no, most databases suffer from alphanumeric fields. These are in theory great as they can store numbers, letters, dates and short love poems. Unfortunately they are also almost useless unless used for short notes and even then the ability to spell-check them is desirable. Simply because alphanumeric fields can contain anything (Often up to the curious limit of 128 characters) people do just that, so for Job Title, you get Professor, Proffesor, Profesor, Prof. prof, prof etc. only one of which is going to come up when you search for Prof. For this reason, wherever possible drop-down fields or other forms of single or multiple choice fields are preferable as they limit the scope of invention to what the database designer puts in them.

    Date fields are for calendar operations such as do-by and reminders and so on, but auto-dating an entire record by having one field fill itself in to indicate the date of creation is a good idea too. Storing dates is like storing most information – if you can’t decide what you specifically want to do with the information, don’t bother recording it as it will fall into disrepute.

    Numeric fields are just that and although they can double as telephone number fields, it is unwise as a numeric field can’t handle Ext. or (+44) Numeric fields are usually for cash sums if formatted for that purpose but can be for other more mechanical reasons. If in doubt ask an engineer rather than a programmer – they tend to have a better handle on the use of databases!

    The last bit in this opening section is on the basic structure of your data. Does your list consist of individual names and addresses (Such as home addresses) or does it consist of names of people who belong to an institution where there might be many people who are resident at the same address? The obvious reason for this is to ensure that for N Berwick High School, you do not end up typing the address 70 times because you have 70 members at that address.

    The other reasons are structural – you could try to address this with contact 1, contact 2 ----contact 70 as separate fields, but then most records would have a plethora of empty fields as they only have, say, 2 contacts. Also if you are searching for someone by their last name, which field do you search on? All of them if you want to avoid missing someone, which is incredibly inefficient when you have many contacts at an address. This is where database structure changes and it becomes what is termed relational. A relational database has the ability to relate many pieces of information (Such as contact details) against any record. It goes further in that it can handle, say, the many membership purchases of an individual against the individual, rather than against the school at which they are a member of staff or under some unwieldy list of purchases which you will somehow cross-refer to the individual members and thence, for correspondence, back to their school addresses.

    More of this twaddle later – we can then cover front ends (Data entry) Back Ends (Data Storage) and Queries (Whatever you chose to do with the data you have stored)

    I have to go and muck out the stables as my daughter’s new show jumper (He’s called Mr Armani!) is somewhat more demanding than was good old Magic!

    Best wishes


    Steve Alker
    Xspirt

  • Posted by steven.alker on 2/28/2010 at 4:31 AM Member
    Dear Juliet

    Nearly forgot to post the second bit - ! Sorry for the delay

    From the ode on how to do it from nuts and bolts upwards and waffle about structure (Which is essential in the long term success of a database) the next things to look at are the front-end and back-end. And then I suppose, because everyone always leaves this to the last, the ways in which you use your data in the database. That’s either query and list or if you want a lot of output, just list. There’s nothing which you can do with database information unless you either list it (That is print out the lot) or query and list it – that is ask a query of the data and then list or otherwise use the results.

    The back-end is where you keep your data. Essentially it can be unseen, but it consists of the fields, records and related records (if appropriate) which you and your members have taken the time to enter into the system.

    The front-end is how you get most of this information into the database and it is usually in the form of a, well, a form! Microsoft gives you a very gentle introduction to these – ask Microsoft Word to look after a mailing list and it will default (Unless someone has stopped it) into producing a very user friendly way of getting the information into the database. Forget about filling in rows in a table or a spreadsheet or appending your information to the end of an email for someone else (Ugh!) to re-type. You are surrounded by examples of forms, so use one of them. How the form is constructed (You could for instance create it in Word and circulate it by Email, but this is not ideal!) its purpose is to safely deposit the user data into a database in the most convenient way possible. Note the word “Safely” How often have you filed in 15 fields in a form only to discover that the 16th requires a bit of research. Oops, you’ve found the information but your other data has “evaporated” This is because data-input forms can be reactive with their database field-by-field or they can wait for you to hit some button marked “Submit” or more likely “Next” Entering data is as easy as you make it or alternatively, don’t make it.

    If you are using the web to communicate, then it makes sense to use web pages to enter data. Tara! You can publish a form from Microsoft Word as a web page and as long as you’ve been careful about how the website keeps its files, it will still work as a data entry form. Its basic but it works. You can do the same with Excel and Access but the latter will try to draw you to doing the job properly through a custom designed HTML (Hyper Text mark Up Language) form from one of its templates. You can literally choose to squirt in the data after completing a page or after completing a field on the page.

    For a member organisation on a tight budget, I would tend towards the HTML data entry form coupled to some robust back end. You will probably have members who are literate in HTML and also have some knowledge of back-end-data structure who are not manic obsessive’s and who could contribute to the ongoing data, database, forms and eventually queries which will make it work.

    The back-end is the work-place of a database as it is where it stores its data, where it allows users to add to or to edit the data and from where you can run queries. The data in a database is almost nothing without a query. You want a mailing list, so you select records which match criteria and then print them or email them. You want some statistics? You query (ask) the database to arrange it’s data in a given way and then either count something or add something or do whatever is needed to produce a % sign in a report. A query precedes a report unless you want the report to contain all the data in the database. This can be annoying when you have promised the prawn-peelers of Annan a report about their own area and you offer them information on everything from Aberdeen to Valasay (Isle of Lewis!)

    For user databases the natural back end is, surprise, surprise, a table or a set of tables if the database has been deemed to be relational. Just remember, that whilst this is the single most important bit of a database, storing the data and processing queries, it is almost irrelevant to the users. A Microsoft Word back end would be a bugger to get information out of, but it would be an accessible way of putting information in. Including using web forms.

    An Excel back end would work, but it would be fragile and prone to corruption, so whilst it is a good idea, It doesn’t really work once you have more than 2 users.

    Microsoft Access seems to offer most of the solutions for a member database in that it has HTML and therefore web based access templates along with being at least basically relational so that you can have the many to one relationship (Like many contacts at a school) but do beware of its limitations. Yes it will take you to a front end and a back end which is comprehensible and given your member bias, you will probably find someone who actually wants to create a structure for you. But beyond a particular size of database and a given number of users (About 10 but it depends)

    I’d say great, do it. The next step up is Microsoft SQL (Standard Query Language) and whilst that isn’t much harder, it is much more rule bound and code bound. I think that the same goes for My SQL and most of the free-form CRM solutions – CRM providers have already solved your problems and have the code, the interfaces and the help-lines to get you working as of tomorrow. What you have to decide is whether that opportunity cost of the time it takes to get a community to work to a common goal for a database or whether you do, like I tend to, to say “Oh it’s worth my while for Salesforce.com to take the strain – I’ll just get on with my job.

    By the way, I’m not sure that a CRM solution is your answer to your question as all forms of licensing involve a cost per user and for a user based organisation, unless the CRM companies have a pricing plan for you, then they price themselves out of the market. That is certainly the case for Maximizer, sadly, whom I used to represent and together we could do anything in the old days!

    In conclusion, I would go for a Microsoft well supported database package which will work from a web-form to a web-query and a web-answer. There are a lot of independent solutions and some of them are good. My only problem with them is “How do they support a bunch of member-users without paying some exotic service fees”

    Steve
    Xspirt
BACK TO TOP

Post a Comment

Related Discussions

Community Info

Top 25 Experts
(Other)