If you have been working with Microsoft Access for a while now, you should be building databases the correct and perceived way but Is Access a relational database?
You should be familiar with the term RDBMS which stands for Relational Database Management System and this is the true definition for MS Access (as defined by Wikipedia) emphasising on ‘database management system‘.
However, DBMS comes in many shapes and sizes depending on the database processes, workflows and the size of the organisation.
Therefore, there are different types of DBMS’s and here are some for you to contemplate and evaluate which doesn’t always mean having to join tables together and form a relationship!
Is Access A Relational Database – It Is But There Are Other Models Too ?
I want to list 5 here (and there are more) in this blog to expand your knowledge or at least awareness and see if you actually or automatically use any of following:
- Relational databases
- Hierarchical databases
- Network databases
- Object-Oriented databases
- Dimensional databases
1. Relational databases is the most commonly used method in designing a database (especially within Access). This is the simplistic approach to joining two or more tables together that have a common field (normally an indexed ‘key’ field) between them making a series of relationships that typically will yield a ‘one-to-many‘ result.
A key field in one table will have a duplicate key field in the joining table that contains a matching value and also have the same data type too. This allows the JET engine to push records into one snapshot (normally via queries) keeping your database optimised improving the performance.
Microsoft Access was designed to be used this way (small to medium sized databases) and is deemed the simplest way to bring data together with little training required. In most cases, the data can be edited without having to redefine structures but this can impede on the performance when comparing to other types of DBMS’s here but remember to apply the indexes to help this process along.
2. Hierarchical databases are considered the ‘grand-daddy’ approach to DBMS systems and has been around the longest that’s for sure. This was therefore developed for the more traditional main-frame systems (pre the personal computer!).
It is still in use today for very large database applications which need to operate a rigid top to bottom (or pyramid) structure. The way this process works, starts at the top as the root having branches below and then cascading downwards (like a tree formation).
This means that the root table is the ‘parent’ record which can have one or more ‘child‘ records (below the root) but a ‘child‘ record can only have one ‘parent‘ record. Each level (hierarchy) can be a parent for the level below and so on.
A search for records start at the top of the tree and then downwards to simulate a rigid data tracking process (used for large search engines – i.e. Travel firms) and this is a rapid a way to find results.
The disadvantage however is each child record can only have one parent (so many-to-many is not permitted) no matter how logical the relationship should appear to be and database structures need to be defined in advance.
3. Network databases are very closely structured to hierarchical databases following a similar concept.
Again, it was typically used for main-frame database systems and was a popular choice during the 1970’s. The main difference really is the ability for a child record have more than one parent which means having a ‘many-to-many’ relationship where applicable making this a little more flexible.
Therefore, this was considered an upgrade to hierarchical DBMS’s to allow for more logical data processing units for searching and reporting on data sets but database structures still needed to be defined in advance.
4. Object-Oriented databases are the newer type of DBMS but are not widely used mainly due to propriety systems been around for so long now and the cost to redevelop a brand new database as opposed to adding to existing systems.
It’s has been designed to replace the larger database which means competition for both network and hierarchical methodologies and can better handle more data type structures which include graphics, images, video and other multimedia formats.
The concept follows the teaching of object orientated programming where re-usable snippets of software can be called and re-used as a new instance. Each object (or class) would have two elements; one being the data type (video, image etc.) and the other being the instruction or method to the data type.
This is really outside the scope of Microsoft Access to be fair but one could adapt this with careful planning and if for some reason you were going to use Access this way it would be for a small system where you intend to capture document scanning, image loads, video and sound files that would be eventually be too large an overhead to store directly into a small database but instead point to external locations. Just be aware of this concept!
5. Dimensional databases are a hybrid of relational databases (RDBMS) which pushes data into a separate and sometimes offline database systems also known as a data warehouse.
You can typically see this type of structure with Microsoft SQL Server database (which Access can link to) and this would be generated typically once a day (or overnight) which will then serve users for reporting out helping to handle and search for large volumes of data quickly.
It’s creates another layer or tier of grouped information for your queries which would include aggregate calculations, facts and other functions for ease of use and help increase the overall performance.
Another term referred here is OLAP which stands for Online Analytical Processing which is another newer level to grouping and summarising your data.
So what about using this in Microsoft Access – Is Access a relational database? Yes, you know it is but sometimes using some of the above database principles or at least borrowing some of the concepts at a lower level, you can control and balance usability with performance especially if you intend to code you application using Access VBA.
Some food for thought here!
Have a good week.
Tags: Access, database modelling, design a relational database, how to create relational database, is access a relational database, rdbms access databases