Guidelines to design an Access Database

[ravikiran]

Hello Gurus,


I need some help in designing a database for one of my applications. I am used to programming in Excel but never worked in combination with Access. And I am quite new to Database Designing process.


This is made for a small company which is into inventory management. They have products that are stored in the inventory. Before storing they got to do certain tasks (checking various things on the products, packing etc.,)


I prepared an excel application which has a status page to retrieve information about each Packing that is received. When PR Number is entered it retreives rest of the information. The page has the following displayed:


PR Number:
Product in the packing:
Company:
Barcode Number:




Checklist


Tasks Status Date/Time Person


Task-1


Task-2


Task-3


Task-4




Task-5








Comments:




Each of the task is a separate module with its own requirements and checks. For e.g. Task-1 has the queries about packing. Is the packing good? If bad what is the problem? Send an email to the person responsible.... etc. All the tasks should be reflected specific to the PR Number.


There might be atleast 5 more columns in each task.


How should I design an Access Database for this type of application? Should I work on a flat database, with all the information put in ONE table? Or should I opt to create multiple tables?


It would be a great help, if you can guide me through this stage.


Thanks in advance,


Regards,
RK.

[Rod]

Hi,

This really is not the place to expect a tutorial on data modelling. There are excellent texts on the topic and plenty of information on-line - just use the keywords data modelling, normaliztion, entity relationship diagrams (ERD) and follow your nose.

However to help you jump start I present a first cut ERD. I emphasize this is a first cut; no way should you base a physical database design on it until it has been thoroughly reviewed.



Walking through what this implies:

• Each company supplies one or more products.
• Each product has a predefined QC template of tasks and checks.
• A task consists of one or more checks.

Now I suspect I have not got this right. There are a host of questions to be answered and the ERD updated accordingly, including:

• Can the same product be supplied by different companies?
• Are the predefined tasks and checks related to the product or the type of packaging?
• Further to the above, are there checks for the product and checks for the packaging?
• Does the packaging differ from company to company?
• Does a product come in different packaging types?
• Are there inners and outers?
• Do any of the check results have to apply to the package throughout its life?
• ... etc.

I have not expressed that last question very well. In say an aircraft spares inventory, each item must be separately identified with the number of flying hours or the number of landings, and so on.

[ravikiran]

Thanks ROD. This helps constructing my thoughts.

I resigned to start going through some material available on Database design techniques and did go through some overnight. That along with the info you provided, will be useful. I am still going to do more research on this for a couple of days and then start the design process. Will get back to you whenever I need some suggestion.

Thanks again.

Regards,
RK.

[orange]

There are free data models at http://www.databaseanswers.org

I'm sure you'll get ideas from some of these models. You should be aware that Inventory/Stock Control is not trivial.