Microsoft Access Database Design Tips - Want To Know 7 Quick Tips To Designing Your Access Database?
I'm often asked How do you design a good Access database? My simple answer is I Reverse engineer it when planning and designing an Access database! Reverse engineer it - What's that? It's not rocket science and it's nothing to do with engineering either.
It is a technique that I have used many times and it works very well for me. The process to a good database design has nothing to do with Microsoft Access (tool) or any other database application, it's a process where you start at the end with Access reports for example and end up at the start with Access tables.
I have a 7 step plan that really works, focuses and clarifies your design objectives:
- Identify the core process - what is it that is key to your requirement? For example, with an inventory database, the key process will be your products keeping control of the stock levels, pricing and tracking their movement.
- With the core process, what sort of reports will you be expected to design and deliver to your clients and other personnel? Plan as many types of reports required including a stock price list, current stock levels and re-order list.
- From your reports, break down into natural units that will be the foundation for your Access database design. In simplified terms, the inventory system might consist of 3 tables; products, stock take levels and the tracking of purchase orders.
- Between the three tables, list all the possible fields that will make up your tables and include a 'common' field which is going to be the link to other tables forming the relational database - one Microsoft Access database strengths.
- Certain fields will need some conditioning to help manage workflows and could include mandatory fields that must be completed, validate certain data inputs to reduce errors and setting a look and feel to the data.
- Creating a plan for users to enter and view data in a user-friendly environment by using Access forms is the next step and Microsoft Access provides a wealth of useful tools too.
- Adding some bells and whistles to your system by providing a starting and welcoming interface to help users navigate which is normally controlled by using Access macros and Access VBA programming.
Hopefully, the above 7 steps of my 'Reverse Engineering' plan will serve you well too as from experience, I know it works because when I've ever deviated from the above steps, things just don't always add up.
Questions and Answers
Article Tags:
access databases
,microsoft access
,designing an access database
,access database design
,good design techniques for access
,ms access deisgn tips
,tips in access database designing
,planning an access database
,building an access database
,microsoft access tips
What do you know about virtualization? Do you care very much about it? If you are a business owner or manager who deals with lots of data and data storage, it might be a good idea to get yourself familiarized.
Making business is a big task that you get into. You should have the idea of how you can increase the traffic of your store and make it more popular. But how? Because of what modern technology can do to us, having your own website online would be such a good idea. You will no longer going to somewhere else to promote your business.
Most CEOs may not realize that data disasters occur every day, and the repercussions can be devastating.
Small business IT support services provide you a real network with proper guidance, realistic ideas, updates, advice so as to maintain your work and competition in the present market.
Sometimes, the DBCC CHECKDB Repair command of SQL Server fails to open Microsoft SQL Server database and database remain unrepaired then, in such situations, the user has to use a third party named SQL Recovery to fix MDF file.
Building an Access database application requires not only knowing and using the key objects which include Tables, Queries, Forms and Reports but a methodology and design impact is just as important. Part of this methodology typically includes optimisation and relational database principles but here I discuss another important process whether you should split an Access database into two different processes.
Building Microsoft Access databases is a breeze especially when utilising the templates and wizard tools on offer. However, a poorly designed database will cause performance issues and start to impede the way you manage that database. Learning to optimise your Access database is key!
The 5 most common tasks one can perform when you are given an Access database that you will be responsible for building and maintaining reports is quite often overlooked and will save a lot of time and guess work in understanding that all 'inherited' Access database system. Read on to find out what my 5 key steps are!
Adding some automation using macros and VBA code procedures to your database system provides a more natural way of maintaining and utilising your Access database system which end-users take for granted and expect. Without this component, a database can be 'clunky' and un-user friendly. The automation processes can handle incomplete values for records, seamless data cleaning procedures, resetting defaults and control invisible routines that we all take for granted.
The Access Form Design (data input screens) are interactive screens that communicate between users and the system. If you going to maintain records for customers, products and place orders, you need an input screen (interface). It is however possible to ignore Forms altogether and simply work with just the tables and queries but this limits functionality and some security issues are potentially exposed.
Discuss this Article
