Personal Database project for scouting: generals tips/advice appreciated!

[Rydra]

This may belong in some other area, but I like it here, so here's where I'm posting.

 

I have decided out of madness to try and build a database, to better manage my scout group.

 

Scout HQ in the UK has promised us a software suite to do this for the best part of a decade; personal info, badge records, attendance, subs payment, event planning, all of it. But they've yet to release anything, even a beta version!

 

Lacking anything else, I planned to start at the bottom, and simply build an access database for it.

With time I'd love to make it a proper web based front end that I can package and give to others to install either locally or on a web server and all that jazz, but first thing's first is build the database!

 

Now the snag is, I've never learnt Access beyond flicking through some awful books/guides over the years, though I have a good understanding of the maths/theory behind it due to some degree level computing study. I've never made, in fact any style of database before...

 

So I'm looking for any hints/tips the experts out there might have that I can implement straight away. Any suggestions, since I'm starting with a literal clean slate for other programs that would work better from the get-go (free, I'm not buying a whole new application package just for this!), or whatever.

 

If there's some fellow scout leaders here, make a few suggestions for what I might include in it!

 

I suspect this will take me many months/years to get fully completed, but gotta start somewhere!

[SYNACK]

First thing is to figure out what information you want to store for each child/group. Once you know that you should look at what you have and see what information is unique. For instance if you were storing patrols of the individuals then you would have a list of patrols and simply have a field in each shilds record linking to what patrol they are in. Building it this way makes getting data back out of it much easier and also makes something like changing a patrol name much easier. This step of design is called Database normalization - Wikipedia, the free encyclopedia

 

You also need to look at what information is fixed and what is changable. For instance if the types of badges are fixed you could possibly have a checkbox collum for each child to set whether they do/do not have it. If they retire old badges and create new ones (I know they did here) then you may be better with a list of badges and a badge transaction table which stores the childid, badge recived link back to the badges table and a date of when it was presented.

 

I think that using Access first would be a good step as if/when you make it bigger/better/web enabled moving from one type of database type to another like SQLExpress or PostgreSQL is not to difficult and you can usually easily port your database structure to the new platform.

[DrummerBoy]

I'd back Synack here, but also add in MySQL - the ability to connect to the web, and link into Joomla and other sites is really useful, plus it's free!!

[chilbs]

Have you heard of https://www.skowt.com/home?

[SYNACK]

I'd back Synack here, but also add in MySQL - the ability to connect to the web, and link into Joomla and other sites is really useful, plus it's free!!

 

MySQL is also good but PostGreSQL PostgreSQL: The world's most advanced open source database is also opensource and has better internal structure and validity checking hence why I suggested it instead. It should also be able to connect to Joomla etc if required. Firebird is also another good one Firebird - The RDBMS that's going where you're going but that kind of choice is probably later in the process. SQL Express is also free SQL Server 2008 R2 Express - Overview if you alrea have the windows platform to run it on and makes migration from Acess about three clicks worth of effort but it all comes down to what you end up wanting to do.

 

@chilbs - that looks like an interesting system.

[Rydra]

ms sql isn't a problem, i have a few copies of 2005/2008 lying around

 

It will be a week or so till i get time to work on it some more, but all advice is good advice, I just don't have to pay attention if I don't like it!

[TechMonkey]

Take a look at escout.org.uk. They have a few systems up and running now that members have used, plus a jolly good place to get program ideas and find out what other groups are doing. When others have suggested making an online records system using mySQL or other it has normally been decide a Bad Idea™ due to the security and DPA implications.

 

Here is the Record Keeping Forum

 

EDIT: On the other hand, if you do make something decent I'll be more than happy to beta test it! Used to run an Access DB but changing to Macs stopped that. All the Excel systems fail due to macros not working the same in Open Office. & make even a local system is on the list of things todo!!

Edited October 7, 2010 by TechMonkey

[Hecate]

I've built several databases in Access but if I was starting a new one from scratch I would head for one of the SQL variants - I'm just getting going with MYSQL and PHP. If you are new to database design Mark Whitehorn's Inside Relational Databases is a great start - it's very readable for one thing and although the examples are in Access there's lots of general stuff too.