Simple database /data storage and analysis solution?

[Energize]

I volunteer at a local charity who have a small office LAN and want a new software system to manage all the storing and handling of their offline data.

Currently they are storing all their data in spreadsheets (no doubt unencrypted), different people have their own spreadsheets they've created, some listing horse information such as name, age, people who have previously loaned them and dates of those loans, certain special requirements, other sheets have staff/volunteer data, others have donor/trustee information, others financial data, mailing lists et al.

Sometimes data gets overridden because two people are altering a file at once.

What I suggested as a possible solution is a standard relational database, with tables representing entities derived from the imported normalised spreadsheet data. Then using input forms utilising SQL to allow users to enter information into the database, and forms to allow users to bring up whatever data they wanted, and also creating some custom queries as well, to expedite common queries. I'd like any data to be able to be output into various common formats such as word, excel, pdf as appropriate too, maybe html/php for the website too. But I haven't finished discussing requirements relating to their website and how such a database could be useful to them in a website.

Of course will need to be able to set various user groups and appropriate permissions. Some way I could remotely manage it too would be great for support purposes. I'll need a decent encryption system too, maybe utilising key-files on pen drives as well as a realistic size password for high level users?

I may be a little out of the loop here regarding databases cos it's been a few years since I studied them but what do you guys think?

Also any suggestions for a decent database software package that a charity could afford, free obviously being preferable, and something that doesn't require a supercomputer to run either please.

 

[Energize]

Use a hosted website that included MySQL with PHPMyAdmin. I heartily recommend TSOHost for this.

You can then use the online forms to CRUD the data in your database.

You can specify permissions.

You can export this data.

You can allow remote access to the database.

You can create a PHP website to show the data.


If you wanted an localised approach, you can use MS SQL Express for free. It has limitations, but there's plenty of support of the web.

You see I had considered a web based approached and use tsohost for my own web hosting but I doubt there's much chance of them going for that, they don't have a huge amount of technical understanding upon initial impression seemed a bit wary about any suggestions of "remote access".

Thus is seems like it's gonna have to be a localised approach.

I didn't realise that Microsoft had any free dbms available, so thanks for that, I'll definitely check that out.

Depending on what you're storing in the database you may need to check the organisation is registered with the ICO, only costs a few quid but it's illegal not to for certain data types.

Only saying this as one of my mates was caught out recently..

I'm dreading having to read up on the legal side, hopefully though I shouldn't need to personally process any confidential information they store.

 

[Energize]

Yeah I'm sure they will have some sort of legal advice, however with regards to security it's not my mind that needs easing, I use Tsohost!

Though admittedly the fact that Tsohost allows one to change the cpanel password without needing to know the previous one via the my.tsohost area, combined with the fact that the cpanel password is also displayed in plaintext above that same input form and is thus clearly not hashed does concern me somewhat.

 

[Energize]

After my recent experience there I definitely agree that there needs to be some mirroring of data between local and web servers. Aside from the fact that the internet connection is unreliable their electric also went off for over an hour when I was there the other day and we can't afford for data submitted from the web server to just vaporise if the local network is down.

Can you recommend any good software for the actual design/modelling process for database projects? I've realised that the technical aspects are the easy part for me, but modelling the processes of the organisation that the database needs to facilitate and things that I can vaguely recall from uni like "use cases" and all that UML stuff for giving a graphical representation are harder for me to take into account. It would be a lot easier for them to understand the structural design too, rather than showing them an entity-relationship diagram!

 

[Energize]

Thanks. I've been pretty careful about making sure whether they will have the information available as soon as the record is created, for example I thought about using the horses passport # as the unique ID to increase efficiency but obviously that's not going to work if that info isn't available at the start.

Regarding server specifications for this kind of project, with only a few local users, what would you recommend, just any old computer running linux with raid 1 and a backup power supply?

 

[Energize]

Thanks for your advice, you clearly have a pretty advanced DB knowledge!

After talking with them, they do enter details before having the passport number as far as the transfer waiting list is concerned, maybe I could suggest obtaining the passport number at the starting point, I'm not sure.

I wonder if "Registered name" would be a suitable primary key for the horses? I can't imagine in reality that two horses would ever have the same registered name at a centre that size, but is it bad design?

As far as the server goes I'll probably be using Linux to keep costs as low as possible.

This is what I have so far regarding structure, just based on the information they gave me. Obviously there's a lot of improvement to be done and relationships added!

 

[Energize]

Thanks. The waiting list is for horses that haven't made it to the centre but the owner has requested they be transferred there. And no, horses don't have DOB, their age is purely based on the year they were born, for example my mare was born in 2001, so she's classed as 12 years old, regardless of month or day.

As for the "work done" etc. I was thinking drop down boxes, because there's a number of common treatments which will be entered that they have given me a list of.

The contactID, is supposed to be the Vet/Farrier etc. so they can tell who performed the treatment.

 

[Energize]

I was wondering, do you think for the server I'd be better off with 2x ~64GB SSD's rather than 2x ~500GB HDD's?

 

[Energize]

Well isn't that precisely what raid is for?

I just remember being told somewhere that the performance of transactional databases is greatly increased when operating using an SSD or RAMDISK.

In any case I'm now setting up a MySQL server on my pc for test purposes.

 

[Energize]

I mean with regards to availability, ie it not mattering about a warning of an eminent failure. I will of course be using a backup, even with all the protections I can put on it, I wouldn't be surprised if it became infested with viruses.

Only a few users. If the consensus is that hard disks are better then I will go with that, but in my example, the SSD costs the same as the hard drive, so there's no cost issue (I imagine the database, OS and software will not exceed 64GB).

 

[Energize]

I've come up with this atm.

What do you think of it?

 

[Energize]

-45 seems short for comments assuming it is freetext. For example, the very short sentence I just wrote, not including this one, is over 45 characters.

I agree, isn't 255 the max for varchar? It doesn't seem like even that would necessairly be enough. Is there a better datatype/object I can use?

And on that subject what about if we wanted to have photos brought up in the views of horses for example, would photos be stored in the database itself as an object/datatype or reference from elsewhere?

-If staffId isn't the natural key (i.e. employee number) it may be worth adding a natural key to the Staff table

Why is this?

-Have you considered having a "Stable" table and then linking to it from horse with StableId as the foreign key as I would have thought there is a 0...n relationship between stables and horses, and you may find yourself wanting to hold more than just the stable name in future(?)

To clarify horses have two names, their registered or competition name like you see on the grand national etc, and a stable name that's used on a normal day to day basis, ie their real name.

-Should passportNo definitely be LONG datatype i.e. will it only ever hold numbers?

Suppose it's a possibility that the datatype could change if passport regulations change but currently passports are all long numbers.

-Assuming sex means gender I'm not convinced it should be BOOLEAN as it isn't obvious what this means i.e. purely looking at the schema how would anyone know if 1 means male or female? This may be parsed in the front end but I would recommend making your database design as unambiguous as possible.

Yeah I've changed that now, I also realised there are more than two genders anyway, ie mare/gelding/stallion, and any potential freaks of nature.

Can a WorkProgramme only have one staff member and one horse?

Yes as it's currently (badly in my opinion) implemented, the table name is probably ambiguous in that regard too admittedly. Every day that a horse is worked the staff member who worked it enters what activity they did with it and the date.

Can a FieldVisit only have one staff member, one horse and one loaner?

Based on the current information I have yes, as far as I'm aware a staff member visits a horse which is loaned to one person. And that visit is entered as a record in the database. That's certainly always the current situation, I suppose that there's a possibility in the future that a horse may have multiple loaners (which would cause problems with other tables too I guess), though I don't see why a seperate visit record could not be added for each horse even in that scenario, to make there only one m-m relationship there, though I suppose that's probably not very elegant in the long run.

Thanks for your advice and questions guys, I've come away with quite a bit to think about.

 

[Energize]

Please could you elaborate? Is horseId not the same as a unique reference number?

 

[Energize]

Can anyone recommend any client software for the end users? Want something that allows the average user who doesn't know anything about databases and sql, entities relationships etc to access and edit data without it looking like some daunting project lol.

 

[Energize]

Or am I just better off creating my own front end? Maybe mod one of those php style ones?

 

[Energize]

also it would be purdent to create an table to record any changes made to the database and who made it for auditing.

Hmm didn't realise you typically have the auditing data in a table itself! But we have for example, made sure every person has their own unique account so such things can be logged.

 

[Energize]

Sounds good, thanks.

I've revised it a bit as you can see (ctrl + f5), but still a couple of questions.

Currently they have vet records, physio records, farrier records, worming records. I don't know how best to represent this, currently I have one treatment record where the type represents the type of record, but that doesn't seem very elegant to me?

Also the same situation exists with contacts, they have vets, farriers etc as contact types, surely there is a better way to represent this than a type field?

 

[Energize]

With regards to creating a front end for the database, what are peoples opinions on web vs desktop application? Advantages I can see to using something like php are that it will work on any device without having to install any software.