Design an Access inventory database around identifiable items, locations and stock movements, then verify the relationships and deployment arrangement. Keep movement history distinct from a current balance and test invalid references deliberately. For shared use, assess the actual front-end, back-end and network setup rather than inventing a universal number of users the database can support.
Begin with the stock question the database must answer. Someone may need to know which item moved, where it came from, where it went and which record explains the action. Write those questions beside the proposed tables so the design supports retrieval and review, not just a convenient entry form.
Which relationships should the inventory model represent?
Microsoft’s table-relationships guide explains the linking mechanism. Access relationships match data through key fields. A primary key provides a unique identifier for each record. Use those concepts to distinguish the identity of an item from its description, which may change during normal maintenance.
A proposed design could give items and locations their own records, with movements referring to the relevant identities. Decide how source and destination are represented and what event makes a movement complete. These are modelling decisions to review with the stock owner before building forms around them.
A many-to-many relationship uses a junction table. Consider the actual relationship in both directions when reviewing the design. A document may contain several items, and an item may appear on several documents. Ask the designer to explain where the individual document lines belong rather than storing a list of item names in one field.
Keep the unit of measure in the requirements. A movement quantity is difficult to interpret if the team cannot tell whether it represents packs, individual units or weight. Agree the intended unit and any required conversion rule with the stock owner, then include the resulting case in the test data.
What should validation prevent?
Referential integrity aims to prevent orphan records and keep references synchronised. Access rejects operations that violate enforced referential integrity. Have the designer show where the intended relationships are defined and which controls are actually enabled. A relationship drawn in a planning diagram is a proposal until the database implements it.
Separate relationship validity from business validity. A movement can refer to an existing item and location while still using the wrong quantity or describing an unauthorised action. List those additional rules explicitly and ask how they will be checked. Retain the source for any operational threshold rather than choosing one simply to make the form appear complete.
Discuss what happens when someone wants to retire an item or correct a location. The business may need historical movement records to remain interpretable. Ask the designer to explain the proposed record lifecycle and demonstrate the relevant behaviour in a test copy before agreeing any deletion or cascading-change rule.
| Design question | Proposed model decision | Test evidence |
|---|---|---|
| Which item moved? | Movement references the item identity | A renamed description still resolves to the intended item |
| Where did it move? | Agreed source and destination references | Movement displays the correct locations |
| Does the reference exist? | Enforced relationship where appropriate | Invalid sample reference is handled as specified |
| Is the quantity meaningful? | Explicit unit and validated business rule | Sample movement retains the intended unit |
| Can history be reviewed? | Defined correction and retirement route | Earlier records remain understandable after the sample change |
This original design worksheet helps the implementer and stock owner review the same evidence. Keep an unresolved requirement visible even when other parts of the model work. An attractive balance report should not conceal a movement rule that has not been agreed.
How should shared Access deployment be assessed?
Microsoft’s split-database documentation describes a specific arrangement. A split Access database separates data tables from other database objects. Each user works through a local front-end copy. Record which file owns the data and which file each person runs so support and updates have a clear target.
Microsoft recommends backing up a database before splitting it. Include the retained original and recovery route in the change plan. Coordinate the operation with the people using the database, and record the approved point at which the test or migration takes place. Keep this separate from normal stock-entry instructions.
Users need Access versions compatible with the back-end file format. Inventory the actual versions and file arrangements the team intends to use. A successful demonstration on the developer’s computer does not answer the compatibility question for another workstation, so include the intended environments in the acceptance record.
The Linked Table Manager can change which back-end database is used. Ask the maintainer to show how the correct destination is identified after a test move or deployment change. Retain the agreed path and ownership in private operational documentation so a front-end copy does not quietly point at an unintended database.
Original item-reference and shared-file exercise
Use fictional records for Sample Folder, Store A and Store B in a separate test database. Prepare an intended movement between the stores and a deliberately invalid reference. The exercise follows identity, validation and shared deployment together, using sample data approved for the review.
First, enter the valid movement through the proposed form. Inspect its item and location references and compare them with the written expectation. Change the sample item’s description and review the earlier movement again. Record whether the relationship remains understandable to the stock owner.
Next, attempt the invalid reference through an appropriate test route. Observe the actual response and record which rule produced it. If the entry is accepted unexpectedly, retain that result and investigate the relationship or validation configuration before changing the requirement.
Then open the intended local front-end copies in the approved test arrangement. Ask participants to locate the same sample movement and explain which back-end holds it. Exercise a representative correction using the agreed workflow and retain the observed result with the versions and file paths involved.
Finally, review how a new front-end version and a recovered back-end would be identified and distributed. Assign maintenance and recovery ownership, and keep unresolved compatibility or network questions in the acceptance record. Use the evidence to decide whether the arrangement suits the actual team and workflow.
Before acceptance, ask a colleague who did not design the database to follow the retained movement from its source document to the report. Record any explanation they still need. This provides useful feedback on field labels and handover notes, while the technical owner reviews the underlying relationships and deployment decisions.
For broader requirements, see business systems and software development. Get started with the proposed tables, stock questions and shared-use arrangement.
By Spout Team. AI-assisted drafting by Codex using the linked primary documentation.