I’ll try to keep it brief.

I’m wondering what the best way of learning how to create a database is. Microsoft Access 2010 would be the final “type” of database, if that makes sense – that’s what we have at work, and it’s a little side project for work I’d like to produce. I don’t have access to this at home though, so would be using the LibreOffice equivalent to do any initial building/testing.

My problem is that whilst reasonably au fait with other aspects of IT (I know my way around vBasic, for example, and I’m currently self-learning C++) I have never built a database before. Used them, yes, but that’s the extent of my experience.

For info, I’m not wanting any all-singing, all-dancing behemoth. It’s purely a way of dragging my workplace into the latter half of the 20th century. I want to track equipment which has faults, record any ongoing monitoring of the faults, record any planned maintenance activity, highlight any mitigating activities/risk assessments, record the results of maintenance activity, and then archive the entry once the fault has been rectified.

Back in the day, this was all done on paper. When computers made their way into the workplace, instead of wondering how best to utilise them to improve the tracking of equipment faults, the paper form was copied into an excel sheet and the only benefit of a result is that no pens are needed to fill in the form. Lots of printer ink though, so not exactly an improvement.

tl;dr – what’s the easiest/quickest way to learn how to create a simple database using Access/whatever the LibreOffice equivalent is? Online resources, books, whatever.

Many thanks.

How likely are the other users to use it? Will it be on a networked drive somewhere? My experience of these sort of things is people find reasons not to use it, e.g. I can’t access from home, I don’t know how to, excels just easier, I use a mac, I want to be able to format and print how I want….

You can address all those issues, but the actual database is simple (I probably wouldn’t use access). The slow bit of DB solutions is making useful attractive relevant reports and efficient usable input screens.

Hmmm. It will eventually be used by maybe 4 – 5 people on a daily/weekly basis, with maybe another 2 or 3 heads of department wanting access periodically. There is no “we can’t access it from home” as work is a remote site and we all have access to a) PCs and b) the network drives.

I’m acutely aware of how a badly-designed data entry form and/or poor reporting can mean people revert to “the old way” so will be keeping this in mind. You should see the actual official maintenance activity database we are forced to use; it is unforgivably bad, and probably the #1 reason that the little task I’m trying to “streamline” has gone back to paperwork and filing cabinets.

Database would be stored on a network drive purely for ease of access, but won’t require access to any networked data sources.

Has to be Access 2010, as that is what we’re stuck with.

Has to be Access 2010, as that is what we’re stuck with.

I’m assuming that’s an IT company policy, as you’re hardly stuck with Access when you could use a free “proper” database like MySQL or postgresql. That’s not to say Access is actually that bad a choice, for a simple data entry / record it’s fairly easy to get the hang of.

I want to track equipment which has faults, record any ongoing monitoring of the faults, record any planned maintenance activity, highlight any mitigating activities/risk assessments, record the results of maintenance activity, and then archive the entry once the fault has been rectified.

You could probably just install Bugzilla and use that. Bugzilla is designed for software bug tracking, but it doesn’t sound that different to what you’re doing. An example bug: https://bugzilla.mozilla.org/show_bug.cgi?id=97493

Other options are available, Jira for example: http://blogs.atlassian.com/2014/03/jira-asset-management-inventory-set/

I you worried about this sort of thing?

http://en.wikipedia.org/wiki/Third_normal_form

To take a different tack…

How much time are you going to spend designing, building, testing and then maintaining this system?

Might you be better buying something off the shelf which you know will work and be robust (and from your company’s perspective, still supported should you leave or end up under a bus…)?

I’m assuming that’s an IT company policy

Yes. We’re unable to install anything at all on the company PCs without admin privileges. I’m guessing that the reason we even have Access is just because it came pre-installed with the computers.

To make it easier to understand what I want to do, I’ll try to explain a bit more:

We have a lot of different equipment at work. A LOT. This equipment is in varying states of repair and equipment not in 100% tip-top condition goes onto a defective equipment register (currently paperwork in a filing cabinet) and has a maintenance job assigned to it (via the abomination of a database I mentioned earlier). Whilst the equipment is awaiting repair, we do a weekly check to track any degradation in condition or performance. This is also recorded on paper and stuck in the filing cabinet. The filing cabinet is in the back of an office somewhere, and itself is in very poor condition. The paperwork and folders are all tatty. The whole process is a shameful throwback to the pre-computer era, and it could be so, so much easier.

I want to create an initial, reference entry for each piece of equipment.
I want to be able to create an entry registering a defect against a piece of equipment.
I want to be able to update/amend the defect entries as and when the periodic checks are completed.
I want to be able to “archive” equipment defect entries when the defect is fixed.
I want to be able to look up a piece of equipment and track its defect history.

It’s not an official task I’ve been given, but I tend to have a fair bit of spare time at work to spend on this. It’ll keep me busy, and it will make at least one of the routine tasks we have that little bit less soul-destroying. If it works, it would be very easy to duplicate for other mundane, routine tasks we have.

Get your design right at the beginning.

It will save a whole lot of pain further down the line. Understand Relational Database Design (that said relational databases aren’t the only fruit).

Understand normalisation, primary keys, foreign keys as a minimum.

http://r937.com/relational.html

If pursuing the Access route check if you have access to the microsoft home use program. Access is (I think) very user friendly for the basics but may be more awkward getting input forms etc as you want them.

Spend plenty of time thinking about the design as mentioned above. It underpins everything.

It’s a while since I used Access but there used to be something very specific about the set up for multiple concurrent user access. If your users need that check on it.

It’s not an official task I’ve been given, but I tend to have a fair bit of spare time at work to spend on this. It’ll keep me busy, and it will make at least one of the routine tasks we have that little bit less soul-destroying. If it works, it would be very easy to duplicate for other mundane, routine tasks we have.

OK, but as mentioned by nemesis don’t underestimate how long this will take you – there’s a reason IT projects go over budget!

Freester has very good advice, too – you really want to get you head round relational DB design, primary and foreign keys, data normalisation etc. BEFORE you start. Once you’ve got 6 months worth of data you really don’t want to have to be changing things…

I can’t see any courses running on my usual MOOCs about DB design, although this one might be worth a go to get you used to how to join tables: https://www.edx.org/course/querying-transact-sql-microsoft-dat201x#!

OK, if you’re going down the self design route…

1. Document it! Don’t leave your colleagues in a mess should you leave.

2. Think about all scenarios that might apply, not just the simple ones. It’s often the niggly situations that make business systems a pain to use – eg:

– can a piece of equipment become more than one piece of equipment – like a bike could be split into a frame, fork, etc, each with their own history, defects, etc. If so, how will that be handled?

– Do you have multiples of a given item of equipment (say a torque wrench) that are essentially identical so will need serialisation to prevent the need to duplicate data on them (so you’d have a record for the torque wrench as a general item and then separate records for each real life torque wrench to reflect its history, defects, etc).

– What legal/contractual requirements are there? Could you be asked to prove the history of a part if there was a problem somewhere? How would you do that?

– Do multiple users need to use the database at the same time? If so, how will you handle that to prevent data inconsitencies, especially if/when you scale it up as you’ve suggested?

– How will you back up the data? Who can recover it? How often does it need to be done?

– Is the process/processes around the use of the db well defined? Who can access the data? Who can change it? When will it be used? Does it cover all real life situations? Will people actually use it or will some continue to insist on the paper/excel forms?

– How much data will you have? Will the performance using existing server/desktop/etc be acceptable, again, particularly as you scale it up?

And so on 😉

And work out a robust backup strategy for the DB. You need to guard against the machine hosting the DB breaking, someone screwing up the data content (and so you want to roll back to an earlier version) etc.

Right, so all that bring me to the following conclusion:

Thanks for the links though. I’ll get reading up on the basics before I put anything to practice.

I’m sure you can do it – fundamentally it’s not that difficult – it’s just all the real world crap around it that makes it tricky and that’s where real world experience (read “having cocked it up in the past…”) really helps. By all means do it as a side project for your own interest but if I was your manager I’d be questioning whether you’re really going to provide value for money by doing it rather than something else which is actually ‘core’ to your business.

Have a hunt around something like sourceforge – there is a good chance someone has already done something similar that you can use as a starting point.
no point in reinventing the wheel

if I was your manager

but as a worker if you can get the time to spend on a project like this it can be interesting and a welcome escape from the usual day to day stuff.

Quite. It’s an odd job, I have. Free time directly correlates with how well everything is functioning. That is, if everything works as it should I do very little indeed other than the mundane weekly stuff I’ve talked about above. I’m mostly here for when things go wrong.

I’ve not used Access to build a DB for a long time but check how the version you are using works with multiple users, in the past Access has struggled with reads blocking writes and overwriting data with older versions.

Typically this happens when user A is viewing a record and B edits the same record. What happens when A then edits the record or simply navigates away is critical to multiple user systems.

If it was me I’d consider using a hosted platform mogrim suggested bugzilla, I’m more familiar with Jira both of which can be hosted. The hosting company holds the database and manages backup while you access the application via a browser.

Jira is < £10 month

That doesn’t help if you are looking for a practice project or a CV builder but setting up a workflow/experience of JIRA is always useful.

did some typing earlier and lost it, in agreement with mogrim

document the processes before you build

I would say that your asset inventory will be one main table – the defect register will be one other big table and you will have a one-to-many relationship (one asset, many defects) between the two

think about the defect register having creation date and completion date and an owner

As Graham says; think about who looks after the database and make sure that it’s not just you, backup etc and make sure that everyone is enthusiastic about keeping it alive (kept up to date) so the user front ends need to be simple to use. Think about who is going to produce the reports that tell you about the outstanding defects and keeping on top of them (managers!)

good luck

Ah but could one defect apply to many assets? Eg if the manufacturer identified an issue on a product that you have more than one of? That’s where my scenario above would be useful in that you could just create one defect for all examples of that piece of equipment.

I was in the same position, upgraded from Excel to Access (no choice).

Access is really easy once you have got your tables sorted and their relationships (links between tables) saved. Access will analyse the data as it’s imported and suggest ways to normalise it.

The data input forms are almost done for you, it’s certainly easy to drag and drop fields.

Access creates a library file to help prevent record locking issues.

Remember to backup (daily depending one use) and ‘compact and repair’ (weekly).

EDIT: this is not to say you can’t spend hours tweaking and honing. I basically created my current role off the back of this.

If you want to stay Windows-y (though MySQL et al would be fine choices) SQL Express is free and will handle up to 10 users more or less like a porper MS SQL server. That with an Access Data Project front end might give you something fun to build and learn without straying to far from the ‘non verboeten’ software stack.

People sell stuff like that as commercial software.

Starting from scratch with access would have to be the last resort IMHO.
It sounds like you need an asset management system – a quick google suggests that there a few open source option. I’d be looking at these first. Good luck and don’t underestimate the costs associated with initial data entry and keeping it all up to date.

Edit – I’d defo be looking at a web based system either running on your own company intranet or look at paying for a hosted version.

Unfortunately, starting from scratch is the only option. Restrictive IT policy means no unauthorised programs can run on our PCs. I really am stuck with Access and nothing else. That’s for the end-user side. I can prepare the database with something else, as long as it can then be exported to run via Access. The SQL Express/ADP suggestion looks like a possibility, but I reckon I’d need IT admin approval and I’m not sure I’d get that for an unofficial side-project. I’ve just had a look at what’s installed on the system and we have something called Mimer SQL. Does that help?

As for the rest:
I don’t envisage ever needing multiple user access, there will only ever be one user at a time using this.
Data entry will begin purely with equipment that’s faulty at the moment, so maybe 30 items right now. Additional equipment would be added primarily when it develops a fault, but also slowly, by me, adding everything else when I have the time.
Handily, each item has a unique ID tag already and the equipment cannot be broken down into smaller components (or at least it makes no sense to do so in the context of what I’m trying to achieve).

Unfortunately, starting from scratch is the only option. Restrictive IT policy means no unauthorised programs can run on our PCs

What would it take for specing and purchasing a new piece of software, and it becoming supported by IT, to be your side project? (Rather than trying to be an amateur software developer)

I’m a bit partial to a self build myself, as company burocracy often means that doing things properly becomes so prohibaitively expensive that it’s easier just to do things yourself or keep doing it the old shit way, but it almost always comes back to bite you on the arse.

You have to be aware that you will become the single point of failure. Everybody else will love the newfound simplicity you have provided but when it breaks it will be your issue and nobody elses. This is great for a bit as it feels like you are indispensible. But then it breaks while you are on holiday and suddenly from being indespensible it becomes “The Flying Ox screwed everythin up”. Or it breaks when you are already busy and you have to spend a load of time fixing things and not doing your day job.

Can you tell I’ve done this in the past?!

Summary: While the self build may seem easy and fun on the face of it, the stakeholder management and business development skills required to buy some proper software may actually be more valuable to your company and put you in a better position long term.

I think you maybe over thinking this a bit based on your own comfort zone in programming.

Why not just copy all the fields from the historic Excel form into a new excel table. Invent some column header names.

Populate this table with either new repairs-items as they come in, or type in some old forms.

Once you have a big enough data sample to be confident of your required field sizes and formats.. Just export to Access-DataSheet at that point and then recreate your excel form. I would create another DataSheet with a record specific to each piece of equipment as well (Purchase date / Purchase Price / Warranty / Training / Power requirement / Maintenance contract contacts etc / Prime user / Room number etc..)

and link the two table using the s/n of the kit

Make it live and rip up the paper

Then only use Excel once a month for trend reporting (from the access table) until you get confidence in that aspect of Acceess

I want to create an initial, reference entry for each piece of equipment.
I want to be able to create an entry registering a defect against a piece of equipment.
I want to be able to update/amend the defect entries as and when the periodic checks are completed.
I want to be able to “archive” equipment defect entries when the defect is fixed.
I want to be able to look up a piece of equipment and track its defect history.

This format works ok as requirements, short atomic requirements.

From these you can identify your entities, i.e. equipment, defect, defect_history.

Then identify the data you want to store against each of these entities, also known as the attributes, so equipment has an id, a name, a description, possible other identifier(s). etc. A Defect may have a code and a status with a registered date and a resolved date. You can then archived resolved defects into the history table.

Create an initial design and some forms for it, use them for a few weeks, then work out what is wrong with it and improve it. Keep this process up for a few iterations personally before sharing the tool.

Once you are happy with it share it with one other person, get their feedback, revise it and re-try, get their feedback again, if it’s all good get another user and so on.

Be prepared to spend a lot of time on your design, but don’t underestimate the benefit of hacking it together and giving it a try (obviously with a duplicate of production data) and be prepared to bin it all and start over if it is wrong.

Summary: While the self build may seem easy and fun on the face of it, the stakeholder management and business development skills required to buy some proper software may actually be more valuable to your company and put you in a better position long term.

+1

Also, going web-based from a hosted system means that you don’t need new software running on your PCs – it’s all in a browser.

The Flying Ox,
I’ve done what you are requiring and alot more complicated TBH in my current position and previous ones (I’ve designed 3 from scratch in Access) in total.
Access is easy once you get your head around it a little. I found The “How to Do Everything with Access 2002 ISBN 0-07-213275-2 an excellent book. I’m sure the latest version will be even better.

As others have said it’s all in the planning 🙂

Restrictive IT policy means no unauthorised programs can run on our PCs. I really am stuck with Access and nothing else.

That suggests that there is no management buy in to your concept and thus it is doomed to failure!

I don’t envisage ever needing multiple user access, there will only ever be one user at a time using this.

If there is more than one user you can’t be sure of this. At some point A will want a report whilst B is entering something or A will have opened something got distracted and left it open and B will want to amend it.

To be honest one of the bug tracking / ticketing systems sounds like it would do 99.9% of your needs off the shelf and free! In fact it will do more, like automatically emailing the “owner” every week to remind them they need to reinspect an item – with a helpful link (or reply by email) to update what they’ve done.

If your people are remote I’d have thought a solution which would work from a phone or a tablet would be ideal for encouraging timely updates.

Buy a book on access 2010, even a dummies guide. It will save you vast amounts of pain

Access 2010 has loads of templates for different sorts of databases including equipment databases. That will get you a start

If you are going to be multi user you must all share a good reliable connection. Access can break horribly over a wan or wireless if users disconnect often with the database open

Buy a book, it will save a lot of pain(yep,I know I said that twice)

For reference,I spend a fair amount of each day with Access dbs. They are easy to create and try to do a lot of the tricky stuff in the background for you but that can also cause you problems later as more and more people use it. It’s not a bad thing to do though and this isn’t a bad use for Access but remember those backups

Personally I think Access is for talented Amateurs anything that requires more than a spreadsheet should be a proper database MSSQL or Oracle + appropriate web front end. If your not an IT person why are you taking on a developer role rather than passing requirements to the IT dept? Restrictive policies are there to stop business processes sitting on poorly developed unsupported software solutions.

Rant over, ‘SAMS Teach Yourself Microsoft Office Access in 24 Hours’ is a good book. Just get a simple few builds under your belt and how to build the one you want for work will fall into place.

Can you treat this as a prototype for evaluation (kind of straw horse demo.) that will allow users to highlight the good points and room for improvement – and allow your design and programming to flourish (but C++, really??) while then handing over to be authored by outside contractor, with then firmly spec’d design. Yourself as project liaison.

Win Win.

Thanks for the updates, folk. I’ve bought a couple of books to keep me from going loopy. I’m finding it so utterly frustrating, knowing exactly what I want to do, knowing that a database is the way to do it, yet not knowing even the first thing about where to start. All the online resources aimed at beginners I’ve found seem to assume lots of prior knowledge. It all feels a bit:

if you’ll excuse my French. I guess this is what Asperger’s feels like.

It’s also quite amusing to see how big these things can become, judging from a few replies. I’m envisaging this as a way to teach myself a bit about database creation whilst helping me & maybe 6 other folk, on a sporadic basis, to tidy up a tedious exercise in paperwork creation. You lot are seeing multiple concurrent users, reports, cloud storage, purchasing departments, business plans. I like it. Think big and colour in the details later on when the project team has been assembled and the money is committed.

And what’s wrong with C++? I thought it would give me a solid foundation to move on to others? What should I be looking at instead?

We’re not, actually – or at least I’m not. Just don’t want to see you being hung out to dry by your bosses when they expand it beyond your tentative beginnings – as it’s often harder to expand/extend something that’s not been designed at that level of functionality (yes, I know about continuous development and Agile process, but).

Can you draw a set of steps as you what everyone is likely to want from the system – enter details, level of permissions, modify a record, view a report on records – and see where the dependencies and common ground lie?

[Edit] – C++ – would consider it as one of the harder to learn object oriented languages – neither C in all its power and fury or C# with the object support – C++ I always found had to be treated very carefully w.r.t. object disposal and memory management which is a bit of a joke these days, with old library support. But that was 12- 15 years ago I was using it. Currently C#, I’m sure there are other languages out there – depends what you want to do, too.

All the online resources aimed at beginners I’ve found seem to assume lots of prior knowledge.

Yep – online resources tend to be a bit rubbish (although not always). Books tend to be written with an overall concept and level of user in mind. Mix that up with being able to preview some of the pages of the book on Amazon and it can really help find something that is at your level.

Access books tend to have to cover two things – designing databases and using Access. Online stuff often seems to assume you already get the database bit.

oh, and as lots have already suggested, play with it a bit to get the idea of how it works but then try and draw out as much of the system as you can on paper before starting. Databases can be a real pain to add stuff to later if you miss out a step early on. Key can be not only deciding what info you need but also what info. you sometimes don’t need. It can be a real p.i.t.a. having to input lots of unnecessary stuff just because the database can’t handle it if it isn’t there

Don’t be put off though – it isn’t as bad as it seems, especially if you already know Visual Basic.

With regard to C++, its horses for courses. Which is the right language depends on what you want to do with it. Java is everywhere including Android. C# is good for windows, ObjectiveC and swift for Apple.

JavaScrip, Python and Lua have a role as does Pl/SQL and Transact SQL.

You can even make a living using “old” languages like Cobol and Delphi.

Writing something from scratch is going to be fun, but it’s going to be a royal pain long term.

I want to track equipment which has faults, record any ongoing monitoring of the faults, record any planned maintenance activity, highlight any mitigating activities/risk assessments, record the results of maintenance activity, and then archive the entry once the fault has been rectified.

If you just want to get on with doing your real job, get something like JIRA.

https://www.atlassian.com/software/jira

You can get a cloud version for not much money.

It will do everything you want with just a trivial bit of configuration and you’ll be up and running with something much better than anything you could do in a realistic timeframe, that doesn’t have bugs that you will have to fix.

I used JIRA at my last job and it was awesome. There are probably similar packages around.

EDIT: Alternatively, if you really want to roll-your-own, Django is good (Python based). C++ is for people who want to prove just how clever they are.

I say go for it – pick up a book on Access and have a play at home / work. You can sign up for a month trial for Office 365 here:

https://products.office.com/en-us/try

Which includes Access. It’s not the same version, but mostly it’ll be templates that have changed. You should try to avoid them if you want to learn how it works.

IT is full of people who do cool things because it scratches an itch. In this case, absolute worst case and it all goes tit up, it sounds like you shouldn’t have any trouble switching back to your paper based version. If it takes off, you have a bunch of requirements encapsulated in your Access version that someone else could work from.