One to One relationship question for inventory management

[keith701a]

I am designing a basic access database for equipment management within our agency. Personnel can only be issued one piece of each equipment type (laptops, vehicles, etc), so I designed the table with one to one relationships as a sort of fail safe ( for example, so if a laptop was being issued to someone that already has one, they'd be alerted to that fact and would look into it further to avoid inventory issues).

Each table has a primary key auto number and are linked by the personnel number as a foreign key. I used the personnel number to create the one to one relationships. A question I was posed, which is my question here, is what is the best design to deal with old equipment that needs to be unassigned? For example, a laptop breaks and the personnel needs to be issued a new one. In this scenario, with the one to one relationship, it wouldn't be possible.

I already have fields to indicate that a piece of equipment is either replaced, surplussed or out of service (which I plan to replace with a single field with a list). One idea I had was to allow blanks in the personnel number field (TLONumber), but then that creates the huge risk of orphan records. Is there a better solution?



This is what my tables and relationships look like:


To anyone that offers assistance, I very much thank you in advance.

[orange]

You may get some ideas from this free data model related to Inventory of IT Assets
http://www.databaseanswers.org/data_...sets/index.htm

The facts behind the data model are listed here
http://www.databaseanswers.org/data_...sets/facts.htm

It is unlikely that this model will satisfy all of your requirements, but it may give you some ideas, and you may be able to adjust it to meet your needs.

As you have found, it is difficult to design your database until all of the facts are known.

You may want to make a list of all of the things you know about the application, and even some things that may apply (either now or in the future).
If you consider all of the things when you do your design, you may simplify "probably changes that will likely occur later".

What happens with assigned assets when someone leaves?
What happens if equipment fails and a loaner or replacement is required?
Is there a "pool of assets" that can be used for "loaners"?

Good luck with your project