Relational Databases:
Relational Databases are the predominant storage format for the data behind IT Systems. They consist of linked (ie. related) tables holding the data, with Referential Constraints defining the links between tables and what happens if the data making up the link between tables gets altered or deleted.
For more details click on the link to the PDF document "Relational Databases - The Basics".
Referential Constraints are essential for preserving the integrity of the data - but can be a major obstacle when trying to deal with inaccurate or corrupt data.
A good IT System will come with a well defined database - but all too often come with limited, if any, tools for working with the database. The bulk loading of data, and the correcting corrupt or inaccurate data, are tasks which require someone with database expertise & tools.
Distributed Databases - The Perils:
Distributed database are not used unless they have to be as maintaining data integrity cannot be enforced as rigorously as it can be done with everyone connected to one central database - but sometimes there is no alternative when some users are based remotely with poor internet connectivity that means it is not feasible for them to connect to a central database.
Replication:
Changes made to the data must be replicated between the central database and the satellite database(s) - but does not always happen as it should.....
Duplicate Records:
Duplicate records can be an issue as it is all too easy for multiple users to enter the same data at different sites without realising - and to then discover that removing the duplicates is far from easy.....
Read more details in the PDF document "Distributed Databases - The Perils".
Troubleshooting Replication Problems:
Writing the data changes to text files that can be sent to the other site and read into the other site's database sounds straight forward - but can fall foul to a variety of problems.....
Files lost in transit is often the most common problem with replication. Resending the replication files, or manually copying them over to the recipient site, is easy to do - but it does require regular monitoring of the replication so that a replication file lost in transit is quickly spotted and dealt with.
If a record has been edited in one database on the same day that it is deleted in another site's database then replication will fail - and this scenario is not an easy issue to fix.....
Troubleshooting replication files that fail to process requires a knowledge of the database structure, and in particular how missing records can cause a replication record to fail. The ability to reinstate missing records is an essential skill when trying to resolve replication files that failed to process.
Excel Automation (Macros / VBA):
Recording Macros in Excel is an easy way of automating repetitive tasks - but it only scratches the surface of what can be achieved!
Visual Basic for Applications (VBA) is programming code used for Macros, and manually writing VBA code can achieve so much more than just automating repetitive tasks.
Loading data from a spreadsheet into a database is one thing that can be done with VBA. This can be useful when initially populating a database, particularly when lots of data has to be entered and the IT System does not have any features for easily adding multiple records - which is an all too common issue with the software supplied with IT Systems usually being designed to allow the adding or editing of an individual record.
Data Migration:
When changing to a new IT System it is often very useful to migrate data from the original IT System.
This will involve the tedious and time consuming process of mapping the tables & columns of the two databases to establish where the data in the original database goes in the new database. Once that is done the process of transferring the data can be established.
That transfer process can be a automated electronic transfer of the data “As Is”, or it could include tidying up of the data. Tidying the data always sounds like a good idea - but it always involves a lot of manual intervention that will be very time consuming and expensive......
Removing Duplicate Records:
Removing duplicate records from a database is rarely as easy to do as it should be! Once the duplicated records have been linked to records in another table they cannot be removed until those links are removed or replaced with a link to a suitable alternative record.
Finding what other tables a duplicate record can be linked to needs the expertise to query the database’s Data Dictionary, then generate the SQL Statements to remove or replace the links.
For more details click on the link to the PDF document "Relational Databases - In More Detail".