This is the most important step and is the process I refer to as Reverse Engineering!
The best way to build the Access database architecture correctly is start with the end result The Reports! by sketching out what reports you want to end up with. After all, database systems are there to serve and we want information please!
As an exercise, if you were migrating from a paper based system or an older database system, you may want to lay out all existing reports on a large table and prioritise which reports you want to rebuild.
Reports for your Access database would include not just the obvious customer list or product list but an order, invoice or even a chart.
There are 5 main elements that you may want to compile to help list the reports required for your Access database system which you may want to note:
1. Each report should have a name (title) with a brief description to help identify, isolate and narrow end-user requirements. This helps with tags and searching functions and features later on (for other parts of Microsoft Access database objects) but you can group and try to match similar reports too.
2. The column headings (fields) for each report should be listed which will serve as a potential map of all fields required. Knowing your fields will serve you well as the structures for your queries (and tables) that it will need to hold.
3. The layout of the fields and any special formatting attributes (i.e. currency style, bold, red, underlined etc) will be a useful exercise as it may reveal how certain values will be stored including their data types. It will also confirm the ‘normalisation’ of the data set (in other words, will the a contact name be one field or possible made from two fields; firstname and surname?).
4. The sorting and grouping of data by a category (if any) will help to organise your data into logical sets and how data will be organised which leads to the decision of understanding and planning that all important relational database (RDBMS).
5. What sort of filtering (criteria) will you want? Write it in simple english as a simple sentence. For example, “I would like to list all customers in the UK for orders raised during January 2011″. From this exercise, you are actually correlating similarities and it starts to define your queries which will be the basis for each report.
Keep the detail consistent (have a template or blue print guide) and be as specific as possible.
This will then help to outline the beginning of the database structure for the next two steps that follow (later in this series).
Each report identified with the fields of that report will help drive where your fields should be stored (in a table).
When you take a closer look at a report, using the concepts of ‘Database Normalisation’, try to split data information into smaller more manageable logical units focusing on a ‘one-to-many’ relationship that is potentially in place.
For example, a customer can place one or more orders and therefore a ‘one-to-many’ relationship is formed between the customer and an order into two (and sometimes more) tables.
Hopefully, the above reverse engineering process starts to shed some light on this important process and you repeat this for each report you want to end up with.
Each report you analyse, should reveal a commonality of tables which will form the database architecture and take you on to the next step.
Before proceeding however, spend as much time here as needed.
This is the most important part of the 7 step reverse engineering plan!
Another tip for you! Identify your main process; for an order document report you may have to hold the customer details (table 1), the order header information (table 2), the order details (table 4) holding the product information (table 5) and possibly the shipping information (table 6) which may land up being 6 different tables.
I invite you to keep up to date with my blogs, articles and eBooks which covers a lot more detail about this article and can be found at https://AccessDatabaseTutorial.com/eBooks.
From Ben Beitler – “Your Access Database Expert”
Article Source: http://EzineArticles.com/?expert=Ben_S_Beitler
There are 7 steps in this series of posts (to follow over the coming weeks) which this being the first explains the database plan of the overall system. To gain a complete and more detailed explanation of all the seven steps, you may want to invest a few £ pounds (with a money back guarantee!) in How to Create an Access Database eBook.