UKH

Some bright spark, cough, suggested at work that a database would be a good idea for a specific project.

This bright spark, cough, has never made a database and does not know the first thing about databases.

I would appreciate any advice on creating databases and best practices so that a database could expand and evolve over time.

This is all for the bright spark, not me, honest gov.

In reply to The Lemming:

You work in the health service don’t you?
If this database is going to contain any personal information on staff or patients I would steer well clear.
I don’t think it’s realistic to build a database complying with data protection and data security rules as a side of desk task.
If it’s for the local fantasy football league, then there are a few options

In reply to The Lemming:

Get real experts to construct databases. Designing and building a decent database is far from trivial
Do not use Microsoft Access (let alone pseudo-databases in Excel, unless the project is really trivial).
Only allow dedicated users who understand the data sufficiently to QC the data to be entered into the database. Otherwise a database can be worse than useless.

In reply to The Lemming:

(First, I can't understand why someone has Disliked this post!)

I'm not a database person but I do know that there are a number of different database approaches possible these days. I think you'd probably need to say a little about the kind of data that you're working with if you want to get good answers. Can you tell us more at the moment?

In reply to The Lemming:

The short answer is, you're going to need to hire a consultant. This isn't really a task that someone can explain here and then you can just execute it.

In reply to Bob Kemp:


I didn't dislike it, but I have to admit that my first response was along the lines of "you what, mate?" I don't really know where to start with advice on how best to use a massively general class of technology to solve an utterly unknown problem. Although I'd guess that the answer is probably going to be a) get a professional to do it, b) find an existing solution that's designed for your use-case or c) use a spreadsheet on a shared drive or something.

In reply to The Lemming:

Usually an excel spreadsheet is the best database for most 'in the office' tasks. Excel used well can be very powerful

Otherwise unless you want to get into some serious thinking about relations, primary keys, integrity of data entry, redundancy of data etc I would not bother. Not understanding these concepts leads to errors and just collecting information which is subsequently unusable. You will need professional help, but the process you are trying to perform needs to very clearly mapped out to make even a professional successful. Expansion and evolution as you desire only comes from a good base - otherwise it will be a complete re-write for some later change and cost a lot more in the long run.

I cannot imagine any right minded IT department allowing you to install a database design and management system on a corporate computer without some serious discussion.

Data protection is a serious consideration as mentioned.

In reply to The Lemming:

From the replies that I have received so far, I think I shall heed the wise advice and bin the idea.

The database would have had personal information and this would open a minefield of data protection issues that I hadn't even considered when I made the suggestion.

I'll just stick with a simple spreadsheet that only my bosses will have access to.

From what you all say,such a simple suggestion could snowball into a huge security headache that could come back to haunt me.

In reply to The Lemming:

You spreadsheet with personal data on it could well come to haunt you. Very easy to leak spreadsheets and their data.

In reply to Lion Bakes:


Then nothing would get done and we would all have to go back to memorising everything.

But I take your point.

In reply to Ramblin dave:


I know what you mean, and you're probably right in what you say. I figured that finding more out about the problem was the first thing though.

In reply to The Lemming:

And hackers will love you for it. But us the public will not.

In reply to The Lemming:

Better still, right out your requirement and take it to the IT department.

In reply to The Lemming:

One of the many crimes Microsoft is guilty of is pretending that databases - specifically Access - can be used effectively and responsibly by non-techy users. That's why they've included it in their Office Suite rather than offer them separately as developer tools. Many years of experience has since shown this not to be true.

Despite huge resources thrown into the problem by Microsoft, and a lot of good progress, it still needs a significant degree of IT professional literacy in order to create a quality database, even in Access. And to know that it's good requires more competence again. That's not to say that Access itself should be avoided at all costs; it's actually nowadays a very capable technology that's well suited to many intranet applications, including those containing sensitive data. But that doesn't mean it should be exposed to the internet in any way - that would require a different level of security completely.

The real problem is not technical as such, but conceptual. To make a good database you need to understand the nature of data and the relationships between different data items. This is not something that comes intuitively, even to bright people, without some degree of training.

If your requirements are something that can be done in Excel, then maybe that's best, as the security implications of being able easily to copy and send data files are pretty obvious. But if feel that you really need a database, I'd suggest professional advice.

In reply to john arran:


I’m not sure it’s all as difficult as it’s painted on this thread. Back in the day (mid 90s), databases marketed at the consumer level came with a decent sized manual that had a very text book like section and a tutorial section as well as a reference section. Consumption of the “text book” part would take a few days and covered all that was needed. A competent person could be up and running in a few days - there’s nothing difficult in the concepts of relational data, it’s just your average person probably hadn’t though about it in such explicit and formal terms before.

The problems arise when the database they made is extended and twisted into solving an ever expanding number of problems, evolving organically into something that is nothing like what a well-designed-from-scratch solution would look like. That is, it sucks, either to use or to maintain.

That problem is in no way unique to databases. I’ve worked to help people from several organisations where a handy calculating spreadsheet has oozed into an unstrustwothy and clunking but business critical behemoth. The same thing happens with things that begin as helpful little macros or programs etc.

The knack is to recognise at the start of a little project when there’s a danger of it suffering creeping scope, and to adopt a more formal design methodology at that point and to make a case for funding a wider solution.

Back to the crime you accuse Access of - I think it heralded the end of proper manuals for consumer level database software and that’s all I can fault it for. Very few users will buy a decent O’Riley guide or whatever to compensate. It remains a really useful tool for many people and good on Microsoft for giving them that. It really isn’t their fault that businesses allow informal solutions made with it to ooze into disasters - there are plenty of other tools businesses would be creating disasters with if access didn’t exist. The problem a I see it lies with a mixture of information illiteracy in management and a less than uniform approach from IT professionals. I’ve known a 2-man-band who produced and maintain a custom relational database application in-house, sustaining what grew into a large business over 25 years, and I’ve seen a team of trained professionals more recently cock up something despite having eminently suitable applications. Can’t blame the tools in the later case (at least not the software tools).

In reply to The Lemming:


The legal implications around data protection are completely decoupled from how you store the data (paper files in a Rolodex, database or spreadsheet etc.)

There are new, tighter rules coming in shortly - harmonising to EU legislation (EU GDPR). I haven’t yet studied the implications of this. The reason that I try and keep abreast of the area is that I want to make sure my professional activities do not stray onto thin ice, and I worry that some people I work for are not well versed in the area.

I’d strongly suggest either boning up on the DPA and the GDPR including relevant legal cases to help you interpret the acts, or getting your boss to have the institutional Data Controller sign off, in writing, on the project specification. That could literally be your get-out-of-jail-free card.

In reply to wintertree:

I'd agree with much of that, and it is true that Access can now be used to build simple database applications with relatively little training. But my point is that it is marketed as a consumer tool, rather than one that still requires considerable application and effort to get right. That leads to a false assumption on the part of users that the wizards involved (some of which are quite impressive) will solve their problems for them (they won't). If the message given was one of "you will need to spend days or weeks learning about data and relationships before attempting to create a high quality database application, but then you might expect to come up with something useful" then I'd say the entire project would be a great success. As it is now, I've seen many people presume they can create a database as easily as they can a spreadsheet, and it simply isn't possible in any effective or responsible way. As a result there is an awful lot of terribly designed, ineffective and insecure Access databases out there, when almost all of them could have been pretty successful if implemented with a little more thought and knowledge.

In reply to wintertree:
I agree with much of your post. The arrival of relational database technology at the desktop in the early 90s was the spark that ignited my first business. It gave me confidence to go it alone because suddenly I could pull all my data together coherently. More to the point I could initiate and manage transactions as well as records. The quick CRM application that I built, exactly as you describe, learning from the exhaustive user manual, was only recently replaced. The accounting suite marches on. That's a testament to the elegance of the realational model and, I like to think, the work I put into the design.

I'm rather sad that everyone is putting you off, Lemming. Databases are great and you can build one without compromising GDPR. Just avoid personal details. How about Munros and Corbetts?

In reply to The Lemming:

If you do work if the health service and you are part of a trust contact your health informatics dept and have a chat. They may be able to create something for you that will be secure and effective and compliant with dp regs. Also if it is relating to staff they can use the Staffid/payrole number that can then be used to link in to other staff data held within the trust if need be.

In reply to BnB:


I agree. There is something exceptionally satisfying in creating an application that makes everyones life easier. In 1993 we were using a system which required the developer to come and see us every time we needed the simplest report and each time at considerable cost. I, aided and abetted by a very fed up office, took the data, moved it to another system and added functionality and reporting which allowed the publication of many clinical papers. We used the system for just under 8 years. For another problem, the satisfaction of using triangle numbers to create large pedigrees in a few lines of code was without equal.

In reply to wintertree:



Until this moment I was blissfully aware of GDPR.

And now that I've done a quick google and read a couple of paragraphs of the Information Commissioner's Office website, I've become exceptionally paranoid.

I don't know whether to thank you all or crawl under a rock.

Agggg

In reply to wintertree:


Without knowing the size and complexity of the database, and the complexity of operations that need to be performed, it's hard to make sensible suggestions.

Most of the doom and gloom comments are probably thinking about enormous, complex databases, with sophisticated relational operations.

A contacts list can be considered a database.

The AWK manual gives examples of how to implement a simple relational database.

It really depends on scale and complexity.

As for Data Protection, any IT department worth its salt ought to be able to provide adequate access control measures, and access rights management to control who can access files.

In reply to captain paranoia:

I generally agree with you - the OP was so unspecific and everyone has their own world view. Mind you I’m sure relational structure is within the grasp of the OP - many of us do it in some aspect of our daily lives without thinking about it.

I said generally agree, but...


Proper compliance with the DPA and the GDPR is about far more than providing access control to the data. That’s really information security, which is needed for sure to meet data protection requirements. But it’s far from the only consideration. Other equally important things unrelated to IT/IS are having valid operational reasons for holding the data, determining time limits to holding the data, collecting and documenting consent where required to collect and to hold the data, determining who has - and who does not have - the right to be granted access to aspects of the data. This is all “paper” design work - the technical implementation details are largely irrelevant to these decisions - the implementation should be bound to enforce them.

That’s where I’d suggest the OP (or their boss) gets approval from the Data Controller before even thinking about the database that will be implemented, at which point IT step in with access control etc.

In reply to wintertree:


Fair enough.

I was thinking more of the comments about hackers, etc.

In reply to john arran:


This is very true, how you design the structure is very dependant on the answers you want from the data and also the performance required.

The data game is very different to what it was even a year ago and continues to change almost day by day, not only technically but also legally.

Lemming, don't let that put you off though, as others have mentioned, just steer clear of any personal data until you know the how to comply with the very new GDPR and DPA, GDPR is essentially about giving control back to the people you are storing data on, their consent for what the data is used for and who it is distributed to, DPA leans more toward the security of the data. Both are very large topics that need looking into if you plan to store anything that could be regarded as personal data.

Incidentally I was recently at a seminar hosted by a large identity management company, they reckon 80% of companies will be non-compliant come the end of next May, when the GDPR regulations come into force, which will be interesting as the maximum penalty for non compliance is up to 4% of global turnover (Turnover, not profit!).

Anyway, it you do want to learn about the technical side of data, I'd personally steer clear of Excel and Access as a learning tool and go for something like mySQL/MariaDB if you want to learn relational dbs and SQL, or look into other data structures like JSON which is often used in noSQL non relational data storage and also pick up some scripting knowledge along the way (Python, php, javascript, etc). It's much easier than it first appears!
There is nothing wrong with excel, I use it almost daily, for fast ad hoc analysis but it's not a database and shouldn't be treated as such. Access is a bit crap really and you'd probably be better off not bothering with it as it's quite limited and a bit of dead end and you'll hit it's limitations very quickly and when you least expect it!

Data is fun to play with!

Cheers
Steve
<script>
var experience = {
"occupation": "data analyst/engineer",
"min_time_served_yrs": 12,
"specialities": ["data analysis",
"database design",
"database administration",
"ETL procedures",
"BI tools",
"enterprise analytics" ]
};
</script>

In reply to wintertree:

Just a pedantic point of information: the General Data Protection Regulation 2016 is the EU legislation, and it is the 'rules' coming in to force on 25th May 2018. It's not harmonising with anything: it will be the law across the whole EU.

Because it's a regulation rather than a directive it becomes law automatically in all EU member states - there's no need to pass enabling legislation in each member state. That contrasts with the Data Protection Act 1988, which was the UK's implementation of Directive 95/46/EC, the EU's Data Protection Directive of 1995, and which would have been mirrored in the other EU states at the time. Because the UK will still be a member of the EU on 25th May 2018 (unless something spectacularly unforeseeable happens between now and then), GDPR will become law here on that day.

What is in the wings is 'replica' legislation that will enact an equivalent of GDPR into UK law if/when the UK exits the EU. You could perhaps look on this as an example of the "continued regulatory alignment" that was being bandied about last week, but it's actually been kicking around as a work in progress since shortly after the Brexit vote. As, I suspect, has a lot of other "alignment"-oriented draft legislation, which it seems has been accepted will be required if any kind of worthwhile trade deal with the EU is ever going to happen. Strangely, no-one seems to talk openly about such behind the scenes goings-on amidst the protracted political and diplomatic car crash which has been keeping us entertained since June last year.


Generally referred to in the GDPR industry as the "legal basis for processing". Contrary to what some people seem to believe, it doesn't have to involve consent. For example, your employer is legally obliged to process personal data relating to you as part of the PAYE tasks involved in doing the payroll: they don't need your consent, and you can't refuse to let them do it.

Using consent as your legal basis for processing does mean that you have to fulfil a goodly number of related obligations. Many of these also tie in with existing legislation called Privacy and Electronic Communications Regulations (PECR), which is currently being re-vamped in order to fit with GDPR. The outcome seems likely to put a bit of bomb under a lot of current direct marketing and online profiling activities (hello, Facebook and Google). It appears likely that the current more or less useless "cookie law" stuff that people put on their web sites at the moment will need to be significantly enhanced.

In reply to The Lemming:

Dammit - one subject I do know quite a bit about and everyone else has beaten me to it!

One thing that hasn't been mentioned above, though, is building any sort of user interface to input/view the data you want to store, which is a whole separate topic covering the "user experience" and business logic that validates what data you're putting in and where and any dependencies.

GDPR is currently a big thing where I work because we DO hold a LOT of personal data in our systems.

If you want to have a look at building a database application quickly, have a look at http://go.uniface.com/trial - it comes bundled with a usable relational database and can be deployed on many platforms and databases.

LBS - 20 years experience building enterprise-level database applications.

In reply to The Lemming:

Hello Mr The Lemming,

Good luck with your project - I have a few comments which I hope will be helpful.

1. A number of folk have posted about data protection. You will have people with similar skills in your organisation; let THEM focus on those issues while you focus on getting things done as you want.

2. Start simple. A spreadsheet is fine - use whatever you have skills in, flesh out the idea, don't try to get everything done at once.

3. Then take it to whichever IT professionals you think will be most helpful (including younger relatives if available), and follow their advice, but only if they keep it simple and move you forward. Otherwise point out the drawbacks of their suggestions, and keep asking.

4. If you have a good idea, it will fly. At that point, be the project manager, and keep the focus on getting results, not the technology or the legalities.

I wish you every success
FF

(hopefully soon to retire from this IT business - I will miss it)

In reply to The Lemming:

I'm still liking the concept of this idea, and I will involve IT if the concept gets a tentative nod.

Can a database hold scanned documents?

In reply to The Lemming:


Most modern databases can, but it can also be effective just to store in the database hyperlinks to where the scanned files are kept in the file system. It's an implementation decision that will depend on many factors such as the sensitivity of your data, the network structure and support available, and the particular database technology used.

In reply to john arran:

Thanks for that.

Which method would be the simplest to implement and work?

I'm not after anything complex when creating, storing and then viewing documents. Could a document be scanned by a phone and then submitted to somebody for consideration to be added to the database?

I like the KISS consept.
Keep it simple, stupid.



These are all non techy questions but I'm hoping there is a way to solve them.

In reply to The Lemming:


That would depend very much on your chosen database technology. Some will support complex objects more easily than others, but in my experience it images are supported, it's pretty easy to do that. What may be more significant is the potential performance hit you could get from a much larger database if it contains all the images too. This can usually be avoided pretty well by careful structuring of tables and queries, but again that's something that will need a bit of learning and application to achieve well.
Maintaining links to images will be less risky from a performance view, but will come with its own difficulties in terms of securing the file system from users except the database user itself.

In reply to The Lemming:

Absolutely - we do it on a literally industrial scale with RTF, DOC, XLS, PNG, JPG, etc. You need to define the datatype of the field that holds the document image such that it can hold enough data. Older datatypes, such as the Oracle LONG, result in using overflow tables, but the newer CLOB or BLOB datatypes don't. Either way, it's worth putting them in their own child table, so they don't affect you adding new fields to the parent.

Standard SQL queries won't work with these fields, though - you'll be into the realms of things like PL/SQL to handle them, or a well-written user interface that can deal with them.

If you are planning on using a lot of documents, your database will grow very large very quickly. As John Arran said, you can put them on a fileshare and hold the path to them in the DB, but that could be a maintenance headache if the filesystem is moved, so you may want to store the path in a config file/table and just the filename in the DB table.

The size of the DB can also mean backups will take forever to run - and that's another strategy you'll need to consider, in case your DB goes pop.

The more modern way is to link to a dedicated DMS (document management system) which may be more efficient at storing the data and will also give you many more tools. Most of our users are going this way. It's worth considering up front, though, as migrating the documents later will give you issues all of it's own...


Yes - no problem there. We also do this a lot, including automated tools scanning folders for new documents.

In reply to The Lemming:


The database you use will quite probably be dictated by cost, at least initially. There are a number around that can be used FOC for ad-hoc personal use, but once you get into commercial use and want something robust, you'll need to think about licensing.

Check with your IT department to see what is already being used and whether you can have a database under the existing agreements - that may well solve your back-up, licensing and support issues in one go.

In reply to LastBoyScout:

To LastBoyScout and John

Thank you so much for the advice.

I will be able to ask some constructive questions now without looking foolish and the replies should say if the project has potential or not.

The database is not on an industrial scale the likes of an NHS pay roll but more admin for a small department.

If it works then there is always potential to expand.

I'm just at the back of a fag packet planning stage.

Thanks everybody, especially for reminding me about data protection.

In reply to The Lemming:

Pleasure and good luck.

There is the usual myriad of tutorials on the web, but shout if you need any more help - you wouldn't believe some of the things I've written databases for!

In reply to The Lemming:

A bit late to this - we have used an application "Filemaker" for decades for small, ad-hoc databases and for some larger ones. http://www.filemaker.com/products/#filemaker-advantage-cross-platform It is fairly user friendly (you can just literally drop = an excel spreadsheet into it, for example). It works on Macs and PCs and there is a server version for serious work.

It's tutorials are quite useful for getting your head around around things like join tables in relational databases, for example. It's downside? IMHO it is bloody expensive per seat, for what it is. And the regular "upgrades" are daylight robbery.

[it may be an Apple subsidiary these days, but you would never guess it - except for the pricing structure]

As an inveterate tinkerer, you may enjoy getting a copy to play with for yourself, if you can stomach the price.

In reply to Martin W:

Great post, thanks.

In reply to wintertree:

You might like to take a look at the the "cookies we use" page on rightmove.co.uk (the link is at the bottom of the home page). This is frequently cited as a example of the sort of thing that all web sites may need to provide before too long - or else stop using so many cookies, which could of course impact their revenues.

Note, though, that on your first visit to the rightmove web site they still offer you the simple "click here if you're OK with us using cookies" option on the pop-up as a shortcut way to make it go away (although they do also include a link to the vastly more comprehensive "cookies we use" page).

There's probably a more satisfactory middle ground somewhere between the two which will allow people to make an informed decision without being baffled by sheer quantity. It's seeming likely that cookies which are required for a web site to deliver its actual function - things like electronic banking session cookies, and "remember me" presistent cookies for web sites like UKC - won't have to be declared. But I can't see people being allowed not to declare third party, tracking and profiling cookies - and as it's really pretty straightforward for even a minimally competent user to find out whether a web site is being honest about what cookies it uses, it's not likely to be in anyone's interest deliberately to lie about it.

In reply to freeflyer:


IFAIK a Health Service Trust has to have a Data Protection Officer. If Mr Lemming's database is going to hold any personal data, he really should contact the DPO for guidance. If he's not sure whether the data that it's going to hold is personal data then...he should contact the DPO for guidance. A pattern begins to emerge...


Always allowing for the fact that if they say "you can't do that" or "you can't do it that way" then they probably have a good reason for saying that, and it's wise to take notice of it. If they're a good person then they will hopefully be able to suggest ways that the required result can be achieved while remaining compliant with the law.

Bear in mind, too, that an individual within an organisation can be held personally liable if their actions cause a data breach, especially if the DPO has already told them not to do it.

Small departmental databases often form part of what is sometimes called "dark data": data that the organisation as a whole doesn't 'know' is being held, and which is a nightmare to control. A related example would be the database extracts that people often save as Excel files in their personal file space for convenience. (There's a story I could tell about that involving a major UK bank...but I won't.)

GDPR has quite a lot to say about data governance. A simple mantra to help keep you out of trouble when considering doing something with data is: "just because you can do something, doesn't mean that you should". That's especially true if the data wasn't originally gathered for the purpose now being considered - that's very likely a direct breach of the law.

In reply to The Lemming:

Whatever problem you are solving, almost certainly there will be a cost effective COTS (Customisable Off The Shelf) solution that could help, have a google around, you could even be creative and use something intended for another problem, for example there are a plethora of task management solutions (Most come with a mobile app and document storage), change a few labels and you have a workable point solution for printer ink logistics (Or whatever you are trying to solve),