Do Senior Civil Servants understand data?
As a database developer and it is with disbelief that I watched the disc story unfold. The story about the two
CD's sent by post that were lost and the office messenger boy who does the carrying things to the mail room was blamed for losing 25 million sets of personal data.
1. There were 2
CD's. This means that the data was at most 1.5
Gb in size. I
dont know what speed the
datalink at the Revenue office is but lets assume it is a bog standard small office link. The download speed could be 20Mb a second,
ie 5000 seconds per
Gb.(less then an hour)
The upload speed would be a 8 times slower.
Secure ftp could have been used to
acheive the next day delivery that TNT was offering.
I think one will find that they have a super fast
uncontended digital connection which would have much higher upload and download speeds.
2. As for the cost associated with filtering this data, It would take me 5
mins to construct a filter - no matter what database format was used, incl. ones I am not familiar with. I would say that there is probably at least 50,000 people in the UK with these particular skills. By the way my
wifes laptop could run this filter on 1.5
Gb of data in less then 10 secs.
3. It is hard to imagine that the person in charge of and
controlling this data does not have these database skills, or is assisted by someone with the skills.
4. The question of whether someone followed departmental procedure is a red herring to avoid the real question, which is about how the procedure was arrived at, in the first place.
5. If the people controlling these databases are so backward in their understanding of data and data manipulation, how could they ever be remotely trusted to be able to
control the integrity of the data they have and the forthcoming ID card databases. The database technician has more control of it then they do.
6. I would bet the password protection on the disc is a widely available proprietary one that could be bypassed
by a $50 download.
Labels: data security, database design, UK ID card
Top 10 Database design tools
These are the Tools I personally find most useful.
MSAccessGreat first database design program. Wizard driven form,button and report generators. Has the advantage of being able to run on most business PC's running Microsoft Office. You can get under the hood with Visual basic and VBA. Can be used in multi user environments. Also good as front end for MySQL and MSSQL databases.
MySQLGreat second database program to use. Install on your webserver and use it for blogging, e-commerce, CMS systems, forums, etc. There are lots of great scripts available for this platform and most are easily configurable. Slight differences in the field data format with MSAccess.
DB Tools ManagerGreat Database management tool. Backup and copy data. Run SQL commands, view data, manage connections,
NotepadOne of the few Microsoft programs that keeps text files clean.
PHP MyAdminWeb based MySQL editor, controls MySQL through browser.
Excel, 123, Google DocumentsA Spreadsheet program for cleaning and preparing data.
Data convertersEssential when working with legacy data.
CutePDFPDF printer and file generator.
FileZillaSolid FTP program.
Dreamweaver, XML SpyFor publishing and creating XML content.
Labels: database design, database design for business
Database Design for business - Part 4 What platform to use?
The decision as to which platform to use will be decided probably by the database developer. Each database designer has their own personal favourite development environment and some developers work exclusively on one platform. I will go through the good points of the main database management programs.
MS Access
Microsoft's database program that formed part of the Office series of software and is now sold
separately. If Microsoft Office 2003 is installed on your office machines then you have everything you need to run an Access database. This is a big attraction as users
don't have to learn any new operating systems or processes.
A database designed in
MS Access can be easily improved by some one else with a little experience and as it is a common platform there are lots of people with MS Access skills available. The development and design side is easily
accessible and user
friendly although a lot of its power is hidden under the bonnet. I have used
MS Access in many
multi user situations and find it suitable for systems with up to 10 users. It can handle
csv data imports fairly readily. I have made systems with 30 million records and found it could still function reasonably. I have even used
MS Access as a
back end database for an e-commerce site. Although there are limits to the number of
concurrent users and if that shop was really busy it could collapse.
Quite complex business processes can be modeled in
MS Access and the reporting side is quite useful and easy to format. There are lots of useful ready made databases in
MSAccess available and bits of them could easily be incorporated into your design.
MySQLMySQL is the number 1 database choice for delivering online applications. MySQL powers more online applications than any other database in the world including such high profile web sites as: Yahoo!, Google,
Flickr,
YouTube,
Wikipedia and thousands of corporate online applications.It is a powerful and scalable platform which means that you can run the same database on more then one machine - need more users stick another server on your stack.
MySQL is
opensource software which means that the licences are free to use. You can buy more feature rich versions from MySQL AB the the
Swedish firm which writes MySQL.
MySQL with Linux,Apache and
PHP is the basis of the famous LAMP stack server setup. Over 60% of all websites with interactivity use a version of this setup.
MySQL is the database of choice for e-commerce and
cms sites. Internal systems run on MySQL run very fast and can handle high numbers of users. The development environment consists of third party programs that link
remotely to the database and allow manipulation of the data and database, the most famous being
PHPMyAdmin which is now owned by MySQL AB.
MS Access can be linked to MySQL using
ODBC drivers.
MSSQL
Microsoft's enterprise level database, scalable, and quick. Must be run on a Windows server, lots of
different flavours and prices. Quite expensive in comparison with the previous two, but the main
difference is the built in transaction logs, and roll back abilities. The security is higher then
MS Access and so is the speed.
This would be recommended to companies with high numbers of users who are happy in the
Microsoft environment. Is useful for website
backends and interactivity.
Oracle, IBM
Similar products to
MSSQL with similar pricing structures £5000 per processor typical price.
Slight
differences in the way that they store information although tools are available that convert one sort of database data to another.
The
differences are fairy esoteric to the average
joe and are really only understood by the developers using them. If your database designer says he must use a particular platform because of a particular feature get them to explain why.
The decision to use a particular platform at this level is usually the
preference of the designer and not because of a compelling feature that one has and other
doesn't. Because of the price of licences involved then pricing is an issue especially in large organisations requiring multiple databases.
Tomorrow I will list my favourite database tools.
Labels: database design, database design for business, MS Access, MySQL
Database Design for Business - Part 3 Working with the designer
Now that you have decided who is writing your database we move onto the database writing phase.
The database designer might initially start work on his own premises and only later in the project will he start working on site. When the writing phase begins make sure that you have a desk and internet connection available for the use of your designer. Remember you want him working not looking for somewhere to plug in. Make sure you have introduced him to all the relevant personnel he will have to deal with and that they know what level of cooperation with him that is required.
When the designer asks for information make sure it is provided promptly and accurately. A proffessional data designer should be extremely trustworthy and safe to entrust access to your data. A database designer who allowed information to escape would not be designing databases for long. A non-disclosure agreement might be applied to business processes to prevent your database designer selling the same system to one of your competitors.
Payment should be made on agreed targets being met. This is where your initial description of what you want done is very important. Most database designers will not embark on a project until an initial deposit is paid typically 20-30%. The designer should have a design path laid out with key completion points and payment schedule inbuilt.
Reporting design is where you will have the biggest input. Remember these reports are why you wanted the database in the first place. Specify the information you want to see before you tell the designer how you want it to look. If it has to fit in with a corporate style make sure the designer has access to the necessary graphics files.
Make sure the designer has all the contact details of the personnel involved in the project. A good practice is to set up an internal blog that everybody involved has access to. This can used by the designer to document the development process also.
I know I have said he and him for the designer, who could in fact be female.
Labels: database design, database design for business
Database Design for Business - Part 2 Instructing the designer
There are two elements to this first appoint the right designer and second communicate your database design ideas to them.
The database designer should understand your basic business processes and not try to add extra processes you do not do already, unless of course you are introducing new practices. To acheive this they should be prepared to visit you at your premises and look at existing practices and figure out how they will impliment your new system, assuming that they think they can. If they suggest that you need extra hardware or software ask them why they are needed.
If you communicate your design idea effectivly to the designer you can more easily set a fixed price. If your ideas are a bit vague so will the pricing. Write a detailed list of the differant elements that your idea requires.
(Customer List - Name, Adress,telephone, e-mail, Accounts ref,fax, delivery addresses, e-mail preferance,credit limit, etc,) Show the calculations that you need to do on the data, and do mock ups of the reports you would like.
The designer should be able to tell you when they can start your project and how long it will take. They should be available to train you and your staff if necessary. There will more then likely be some issues and unforseen circumstances that will need to be patched up when the system goes live. Will the database designer be able to offer remote assistance or onsite help?
Get the designer to show you some other projects they have completed. Try and see more then one designer! Some designers speciallise in differant industry sectors but this is not something that you should give to much weight to, as long as your designer understands your business processes.
Next we will look at the technology choices available in Part 3.
Labels: business, database design, database design for business
Database Design for Business - Part 1 Getting ready
Define and Refine and then Define againOk so you have a problem that you think you can solve by getting a database written.
Well the first step in having your own database is to define the problem/task you want it to do. This requires more then an outline of the problem/task. As databases are just a method of dealing with various bits of information, this means looking at everypiece of information that you will need for your project. Where does the information come from? What calculations need to be done on this information? Where is this information going? Do we need to print anything? Are we publishing to a webserver?
At the early stage of development we need to look at how many users there will be on the system?
How many different transactions will we be recording? How critical the system will be when it is in place? This information will help us to decide which platform to use. By platform I mean the program you or your database designer will use to design your database. The platform or
database management system (
DBMS) is computer software designed for the purpose of managing databases. Typical examples of DBMSs include
Oracle,
DB2,
Microsoft Access,
Microsoft SQL Server,
PostgreSQL,
MySQL and
FileMaker. DBMSs are typically used by Database designers in the creation of Database systems.
Here are the top 10 things to do if you are considering buying/building your own database.Be realistic about the number of users, transactions, etc, and about how the workload will grow in the future. There is no point using a huge Rolls Royce engine when a small Honda will do, and equally a small Honda when you really need a big Jet engine.
Keep focused on exactly the immediate job it needs to do. Don't add unnecessary bells and whistles. The most successful databases are the ones that kill those little repetitive jobs that eat up you and your staffs time.
Format the data to a form that can be easily incorporated into your other systems.
Draw a diagram of how the data is collected and input
(
Jim in the warehouse enters the incoming delivery details into a form on his computer, the database interrogates the clocking in machine, whatever.)
Continue the diagram to show what happens to this information
(
Ann in accounts matches incoming invoices with Jim's delivery notes, The database works out wages based on a table of employees and their pay rates.)
Then show the output of the database
(
A report is emailed to the MD to tell him the value of uninvoiced deliveries in the warehouse, the database sends weekly wage figures to the Payroll program for processing.)
Take a long look at the existing input procedures you are using (we are assuming they are successful) What parts of the process can be speeded up? (
Is Jim in the warehouse having to type in the same address 30 times a day?) As a general rule ask the person doing the job currently what takes them the most time and invariably they will immediately describe the most boring and repetitive part of the job and is usually the first problem the database should address.
You already know the calculations you make on a daily basis and funny enough a lot of them are boring and repetitive no matter how fancy the mathematics is. So if your database can collect all the figures and variables you need for your calculations why not let it do the calculations for you.
Is the incoming information accurate? Does the database need to enforce any rules on the incoming data?
How many people will be using the system, Where are they using the system from? How secure is my information?
Different levels of access to reports and information.(
Does Jim in the warehouse need to know what you are paying for stock?)
Know what benefits you are bringing to your company if the system works as you imagine. (Increases in productivity, lower costs, quicker process, and what that is worth in financial terms.)
If you do these things you are ready to start talking to a database designer in general terms about your project.
I will tackle this question in Part 2 Appointing a designer
Copyright 2007 node-net.comLabels: business, database design, database design for business
