UKC

I'm looking for advice on our IT requirements - and where better to go for advice than ukclimbing, eh? (actually don't answer that...)

I need to knock up a relational database for somewhere in the region of n=250. It will contain confidential information and needs to be encrypted. It will be hosted on a local (very secure) server and well backed up etc.

It also ultimately (but not necessarily immediately) needs to be able to do mail merge, automated emails and appointments via Exchange (2010) and auto populate assorted form letters etc. As a bonus the ability to send SMS reminders etc would be great (we have a kindly switchboard who will allow this).

Once established our local IT guys are happy to troubleshoot but they don't want to set it all up. I had a guy lined up to do it in FileMaker Pro (and he has done very similar projects previously so I know he can do it well in this framework) but he's moved posts and is no longer available. Quotes from the (main) IT department are prohibitively expensive.

I am aware that this can be done, as another similar department has enacted this in FMP where it runs quite happily...

So the question is: for someone with no particular experience in this are, how difficulties this to set up? I have no particular 'IT skills' beyond a standard postgrad level of computer literacy and a willingness to try different things... I certainly don't do code but occasionally copy and paste things in to Terminal without too many issues!

In terms of clients not many - 10-15 of which about 80% are OSX 10.8 or 10.9; iOS, and w7 on a couple of machines. Would probably need to be able to use a tablet out of the office and then sync on return. Wired ethernet with a fast connection to the secure remote server.

so:
Is FileMaker Pro the best choice?
Can I realistically expect to be able to set this up and scale it as needed?
Can I (or someone more experienced) add the links to Exchange, SMS etc later without building it in from the start?
Is this liable to take me 20 hours or 200 hours?
Are there other platform agnostic online systems that could do this at a reasonable cost?

All comments form the good folk of ukclimbing gratefully received!

Cheers

b

In reply to ben b:

You must be joking. Just a few points: you don't 'knock up' a relational database, you design it. For a relational database to work properly your data should be normalised to at least 3NF - and if you don't know what that means then you need to find out. I don't recognise your term 'N=250'.

Tasks like 'mail merge, sending SMS' etc are not part of the functionality of a database which is the platform for storing - ahem - data. You need a language to do that.

I'm not familiar with Filemaker, I am very familiar with Access and although it has some limitations it s still a vastly underrated tool for RAD. A better approach is possibly to use SQL Server Express - which is free, but as good a true relational database as exists - with an Access front end. Better still is to develop your application using stored procedures on the server, which can then be used wirelessly, remotely over t'internet etc, but that requires someone with good T-SQL skills.

I don't understand what your switchboard has got to do with sending SMS? That's usually implemented by calling an online service.

Syncing can be a nightmare, depending on the complexity of data and how well structured your database is.

200 hours should just about be enough to expose you to world of pain which will be never ending.

In reply to Rob Exile Ward:

I was joking about the knocking up bit

I think perhaps I have been sloppy in explaining what I want it to do - which is store information that can be readily queried not on a massive and complex scale, in fact fairly simple 'pull details of everyone who has x and y but not z who is over 16'. An additional bit needs to communicate with Exchange such that details can be pulled in for mailings etc but crucially that isn't needed yet...

Currently this is done on a mixture of Excel and a really old Access system that I haven't really used much.

n=250 is a bad epidemiology habit relating to 250 individuals in the database.

Switch have easy access to a reliable online SMS service which has the major advantage of not costing that much!

I think the 200 hours of pain might be accurate though...

thanks for the prompt reply!

b

In reply to ben b:

I've used Filemaker for exactly this sort of task before. It took me less than 20 hours to set up and I'm by no means a skilled IT person.

In reply to ben b:

OK, maybe I was a bit tough... However it is remarkable how quickly the boolean algebra for 'pull details of everyone who has x and y but not z who is over 16' can spin wildly out of control. The key to that sort of process is data normalisation and a SQL based database - that's what SQL is designed to do.

Normalisation isn't intuitive, and most developers get it wrong, not necessarily their fault, just that they may not have been exposed to the theory when they were acquiring their skill set. Worth putting a bit of effort in though.

If you've already got Access why not continue to use it?

In reply to Rob Exile Ward:

I think it's Access 97 and I don't think we have a machine that will open it any more...

b

In reply to Tall Clare:

Good to hear a range of opinions although I suspect you may be carrying a bushel as well.
Sums, stats and logic are not my best mates!

b

In reply to ben b:

Grab a cracked copy of office 2003 and convert the old db from 97 to 2003 and then 2003 to 2010?

In reply to ben b:

As a tech moron I found Filemaker easier than Access for assembling databases - my understanding was that whilst Filemaker was easier for the tech moron, it didn't have the scope that Access has.

In reply to ben b:

I'd agree with Tall Clare - it's very easy to make a quick and dirty database in Flemaker - you can just drop a csv file or tab delimited file into it. At a base level, 20 hours will get you pretty familiar with how to do queries in a user-friendly way using a variety of different views in to the data, It comes with some moderately useful templates out of the box and there are also tutorials (for a reasonably modest fee) that can take you up to a fairly sophisticated level.

The server version is a bit pricey, but gives sophisticated backup and user configuration options, Then we are talking 200 hours! And some programming skills in Filemakers rather arcane scripting language which seems to be nothing like anything else I've ever used.

I'd also agree that you don't "knock up" a relational database! it takes some design and understanding of how databases work. At least if you want it to be useful. However, you can put something together quickly to see how it will work. The underlying tables still exist and can be reimported if you find you have made a pigs ear of it.

In reply to ben b:

Filemaker is capable of some fairly sophisticated solutions, but you will often have to do things a long way around if you are used to more traditional programming. However if you intend to deploy to the iPhone or iPad it is the bee's knees. I haven't done anything large-scale in Filemaker for some time as it doesn't do large scale transformations and manipulations easily in code. Filemaker's security and encryption was improved considerably in the last major release.

HTH SS

In reply to SouthernSteve:

I'd forgotten the iPhone / iPad emphasis - totally agree. This is great. Early iterations had the iPhone iPad application as purchasable like the desktop client software, but I think they came to their senses and realized it could drive sales if it was free. The most recent iteration looks to have very nice tools for setting up the layouts for this - I haven't plunked down the hideous amount of money for an upgrade (they are as bad as Adobe in this regard), but I'm slowly getting my head around the cost....

In reply to ben b:
You also need to think about whom the encryption is protecting against. Most think it's against outsiders, but as the recent Morrison's HR data leak shows, it's often insiders who leak data. So separate duties, the person who has access to the server the data sits on, does not know the encryption pass phrase. The person who knows the pass phrase does not have access to the server. So it takes two if them to work together to open the database. It takes two of them to work together to copy , decrypt and steal the data. You also need to think about audit. Log accesses to the data to another server to which the DBAs do not have access, so they cannot delete the logs. Log all privileged access to the back end server. It takes three to steal the data and hide the audit trail etc. etc. Log all user access and queries run. Use stored procedures rather than user adhoc as previously stated.

Encryption is not a solution in itself, but needs a few well defined processes and separation of duty to make it work effectively.

In reply to ben b:

I use https://www.knackhq.com/ for knocking up simple databases. quite quick and simple. havnt used file maker for about 10 years s oi don't know how it compares with the modern version the big + side is that all the ongoing admin and backup is someone else's problem.

In reply to james wardle:

Thanks everyone. Interesting stuff and good to hear a number of different views.

Due to the nature of the data we probably can't outsource outside of the organisation so whilst the knack site looks really interesting it probably isn't going to work. It looks like I might need to explore a small FMP database option as a standalone that can then be incorporated into a larger FMP Server project with all the required connectivity later down the track - by someone else.

Much appreciated and thanks for helping out with my luddite queries,

cheers

Ben