Normalisation

[joannax]

Can anybody please help I'm trying to produce a simple PC inventory database, and I'm not sure to whether the normalisation is totally correct,and any help would be appreciated, the user can use many PC's as he/she
can log onto many PC's, however the user will only be allocated one PC in his/her name, and the PC's can have many Peripherals..etc

Please find below the tables.

Best wishes,

Joanne

PC ID
User ID

User ID
Department ID
Section
User Name

PC ID
Processor
Pent
Ram
CD RW
Peripheral ID
Monitor ID
Hardware ID

Peripheral ID
Type
Support Pack
Network Status

Software ID
PC ID
Office Version
O/S
Outlook Vers
Other Software

PC ID
Department ID
Network Name
Purchase Date
Source of funding
Remarks
Replacement phase
Replacement Spec
Date Replaced
Software ID
Hardware ID

Monitor ID
Size and Type

 

[Lightning]

Hi Joanne

At first glance your structure appears OK. Is there any table in particular that you are concerned about, or are you just looking for confirmation?

Your table with PC ID and User ID may not be necessary. Since each user will only have one PC ID listed against them, this step could be done in the User ID table. The only reason I can think of for keeping these two IDs in a separate table is if you are using that table as a lookup source for a combo box at some stage, and even that could be done from the USer ID table. But that is a personal idiosyncracy, not a normalisation requirement.

Access includes some templates for creating databases from scratch. One of these is an Inventory db. Have you considered using that? Or even just using it as a reference as you design your application?

Your table structure as listed will certainly provide normalised data. Go with it.

Have Fun

Lightning

 

[JeremyNYC]

Joanne,

Looks like you're off to a good start, though are a few things I would change (and it would have been helpful to have table names!):

Get rid of the spaces in your field names. This will make your life a LOT easier.

In the PC table, what is Pent? It looks like a duplicate of Processor. And the CDRW field might be more useful if it were more generic than a y/n field--maybe it could store what type of optical drive the computer has, including the possibility of "none".

Also, if a computer can have more than one of the following, then these fields should be removed from this table, and a PCID field should go in each of the related tables.
Peripheral ID
Monitor ID
Hardware ID

The software table seems a little shuffled. I would have things like OS, office version, anything else that you'll have on every PC in the PC table. Then make a software table that looks more like this:
tblAdditionalSW
===============
PCID
SWName
Version
Patch

It looks like the third table and the second-to-last table should be combined, as they're both describing PCs.

And in the last table, those should be separate fields.

There's a long article on my website about database design that I recommend to all new databasers. It's written by Paul Litwin, who co-authored some of best books on Access I've seen (Access Developer's Handbook series).

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.