Equipment inventory database design (1 Viewer)

[David8]

Question about equipment inventory database design

Hello,

I am a relative beginner with MS Access. We are trying to create a database of all IT equipment in our large-medium size organisation. I have read about database design a bit and played around with the program, but I am facing a number of challenges:

There are different types of equipment eg computers; monitors; printers; projectors etc. These all share some crucial fields such as manufacturer, serial number, color, model number etc. But they also have unique fields: what is the diagonal size of a monitor? Is a projector lcd or dlp? Is a printer capable of double-sided output? How much RAM does a computer have? That is why I have kept separate tables for each type of equipment, with relationships to lookup tables such as "Manufacturer" and "Location". I hope this is the right approach.

But lookup tables and relationships do not solve all my problems. I NEED the database to 'understand' that there are not printer serial numbers and monitor serial numbers, there are just serial numbers and it is a common field throughout the database. BUT when a user enters a new serial number in the Printers table, I do not want them to have to enter it in a Serial Numbers table first, then enter the serial number again in the Printers table. I only want them to enter s/n once.

We have our own Inventory Number system. They are unique and hand written discretely on every item. Just like serial numbers, the database must recognise these as a 'common thread' that runs through the whole database. There should logically only be one "Inventory Number" field, but it must be represented on every table.

Should the Inventory Number be a DBID replacing the auto-generated one?

LATER EDIT: I suppose I was asking 'should I make our Inventory Number a primary key'. And the answer is that the Inventory Number is a candidate key. However since people can make mistakes like accidentally writing the same Inventory Numbers on two different pieces of equipment, they sometimes need changing. So it is better to have a separate auto-generated primary key.

I want to be able to run a query that pulls in all inventory objects and lists them with their shared attributes such as their location, inventory number, manufacturer, serial number etc.

Such a query I would want to be able to sort first by location/room and then by item type. This makes it easy to walk round and check the inventory is correct. To sort by item type that would need to be a field, would it not? But in the computers table, type is always computer. In the monitors table type is always monitor. It would be tedious to have to enter "type: projector" every time you entered a new projector. Can you get Access to recognise that "this is the projectors table, everything here is a projector"?

How would you design this database?

 

[David8]

I think you are saying that my division of the data into separate tables for different data types is a response to the problems of data entry. However it is the wrong response because I should be getting to grips with forms?

I think you are saying that scrolling through reams of fields which are irrelevant to one particular equipment type is not a problem if you create dedicated forms for each equipment type?

Thank you for sending me your example, but as far as I could see it doesn't match my situation because the only type of entries were people, and they always had the same 3 attributes, namely favourite movie; colour; food. But I have printers, computers, monitors etc and they all have not just different values for their attributes, but entirely different attributes. For example printers are either inkjet or laser. But for a computer the question of whether it is laser or inkjet is not valid. However you are suggesting putting printers and computers in the same equipment table.

Can I create different forms for printers, monitors, computers etc that all feed through to the same equipment table? Can the printers form completely ignore computer RAM size, computer CPU speed, computer HDD size, computer form factor and all the other fields that are irrelevant to printers? Even though that form is posting data to a table that includes these fields?

 

[David8]

No, I'm not saying that because of data entry or such. I am saying this because PROPER DATABASE DESIGN - Normalization demands it.

Point taken

What I'm saying is that your table structure has nothing to do with data entry. You can make data entry to fit anything so you need to not focus on the entry method at this time.

Maybe we were at cross purposes. I was actually trying to own up to a beginner mistake. Completely unfamiliar with forms, I was admitting that I HAD made the mistake of allowing my useage of tables to be dictated by data entry, because I was using them for data entry. I can see you are saying that I should not be.

If you can't take the time to think about the design that I have in the sample, then I fear you will not be ready to think about your proper database design. A little bit of imagination could turn the PERSON into EQUIPMENT and the attributes into Serial number, screen size, etc. And you missed that you can add any number of attributes to one type of person but not the other (without many unfilled fields). If you would take the time to play with it you will see how you can add many different attributes (not just color, food or favorite book) to the PERSON (EQUIPMENT).

No actually, I am not so stupid that I did not see this. I need to own up to being almost COMPLETELY unfamiliar with forms, and only just beginning to realise that their proper use is one of the main things I am missing. You need to see that is where I am coming from.

If we take the example of people, then I do not want to add attributes AD HOC from one person to the next. I want to have very clearly predefined TYPES of people. The presence of certain attributes such as serial number would be unaffected by TYPE. But as soon as I select a TYPE of person (or equipment) I want that to automatically switch in the relevant attributes to be entered.

I want a PRESET range of attributes to be available in a form for each TYPE of equipment. I do not see a mechanism to do that in your example. If I have missed it, please forgive me and point out where it is. In any case, I do not want to criticise/comment on your example for having or not having this feature, but it is a feature I would like to master.

Is it best achieved by a special field TYPE within a form which affects which other attributes are shown in that form, OR is it best to have a seperate form for each equipment type?

 

[David8]

So I would ask you to read the links provided, and pay close attention to them.

OK, I am reading them. I actually completely missed the hyperlinks first time, as I am pretty tired.

 

[David8]

Thank you for this latest example. There is now a drop down list 'pool' of attributes, which varies depending on whether you pick laptop, monitor, printer or whatever. That is great.

But this gives the user the option to pick and choose which laptop style attributes they are going to enter. I won't want a drop down for "cboPrefID". If there are 5 possible attributes for laptops, then I want all 5 rows filled in under cboPrefID as soon as I indicate a laptop, so that the user is as at least forced to consider entering data for every relevant attribute.

This is taking me more into the detail of form design. Thank you for pointing me toward the right track, and for the articles that explain key database principles. I shall now focus on my own effort with form design, but if you would like to respond to my previous paragraph with a revised example that would be brilliant.

I think I should go away, play with forms, then perhaps take any further question onto the forum section for forms, if I have any.

 

[gemma-the-husky]

david

ignore forms (well think about forms - but its not about forms)

Its all about data

you have assets. you want to categorize those assets. so you have a single table for the assets, and distinguish different types of asset by some identifier within the table

once you get this correct, the forms are much easier to prepare.

I am sure that is the way Bob is leading you.

 

[David8]

Dave (G-t-H),

Thank you for your help. Actually this database already exists. It is fairly well developed.... in somewhat of the wrong direction. Merging the existing monitors, printers, computers, laptops, projectors, switches and other tables should actually not be massively difficult. Nor will adding a tblEquipmentType creating a Type drop down in the main table.

As I see it now, I will eventually have my big tblEquipment and then a few lookup tables such as Manufacturers, Room (for location), Type, and a few others.

But I am worried (at the moment) about getting data entry and data editing/ammendment right. As I see it tblEquipment will have too major types of attribute: 1) universal attributes such as 'manufacturer' or 'serial number', and 2) type-specific attributes such as 'laser vs inkjet' or '3:4 vs 16:10 vs 16:9' or 'HDD1 SIZE'.

I do not want the person entering data for printers to be faced with long rows of computer or monitor specific fields that are irrelevant to printers. But I do want a data entry method that once a printer is indicated, all the relevant fields are 'shoved in the face' of whoever's entering data. I do not want them to fail to consider a relevant field.

With a seperate table for say projectors, and without the use of forms, that was easy. If you were entering a new projector you went to the projectors table and were presented with all fields relevant to projectors. No more and no less. With an 'all-in' equipment table I am less sure how this will work. I feel that forms will become crucial in getting the data entry experience back to as good as it used to be or better. Remember that with our current setup entering new data is a breeze. You go to the table relevant to the item type and tab your way along the bottom row considering each field in turn as you go. I do not want to lose this.

There are fairly regular equipment moves, with new equipment coming in and older equipment cascading through the organisation. Recording of movement and disposal is not disciplined at the time movement happens, and I am not senior enough to change that state of affairs. The only solution is regular equipment audits, which can involve lots of data ammendment. This involves walk-about on a large site peering under desks etc. If data entry and ammendment is not super slick (I'm not saying it is quite that even now) then the audits are very tedious and don't get done often enough.

Would it be best to have one main form with a drop-down field 'type' at the top, with the rest of the form changing based on the value entered in the type field to suit which type had been indicated?

OR, ALTERNATIVELY should I have dedicated forms for each of the different equipment types all entering data into the same tblEquipment?

 

[gemma-the-husky]

A few ideas

Either - show/hide/ or maybe grey out all the relevant/non-relevant controls depending on the asset type. This will need a little bit of code.

Or - Have a variant subform to enter the details, and show a different sub-form depending on the asset type. Probably a little bit harder to code, but it may look better

Or as you say - You could have a different entry form for each type of asset.

just try different things til lyou get something you like
-----------------
one other thing - are you and your users entering data directly into tables? this is not recommended. You will end up having lookup fields embedded in your tables (which are not recommended) and you lose the ability to control the input by careful validations. You ought to use a form for input, and insulate your users from the tables. Even if its you doing the input.

 

[David8]

one other thing - are you and your users entering data directly into tables? this is not recommended.

Yes we are (soon to be 'were' I hope).

Actually the Inventory was originally (several years ago) in MS Excel. We did realise some of the benefits of using a proper database. But we were inexperienced in database design, and allowed considerations of data entry to affect table design.

You will end up having lookup fields embedded in your tables (which are not recommended)

We do I'm afraid

and you lose the ability to control the input by careful validations.

Please explain further.

You ought to use a form for input, and insulate your users from the tables. Even if its you doing the input.

Yes I am realising that forms and queries are for the human being, but tables are to be designed exclusively for efficient use by the database/computer.

You can have code add all applicable child records to fill out. I will attempt to modify my last example to show you how, if I get a chance today. I am currently on the bus on my way to work.

Thank you for your interest, even from the bus! Thanks for offering to ammend the example. If I make any excuse for our use of Access, it is that coming from other programs in the MS Office suite, we/I wanted to be able to rely on its 'GUI' functionality, but struggled to get it right with that alone.
Edit: If I use a separate form for every asset type, we may in theory be able to do what we want without resorting to code. I'll see if we want to go down that route.

It seems that with Access if you want to do anything at all advanced, you will have to start coding sooner or later. I shall just have to accept that I suppose.

 

[gemma-the-husky]

validation/entry in a form

any number of reasons to never use tables - for example

if you enter data in a form, you can check the sense of fields in a detailed way - this is only possible in a rudimentary "blunt" way if you enter directly in a table

eg -
you may store a cost, and want to warn the user if the value entered is outside certain bounds, or you may want to check that dates entered make sense.

you may want to use a calendar/datepicker to select a date

you may want to provide your own warning messages in the case of errors

you may want to give this to unsophisticated users, who dont know how to use tables directly

users can delete stuff/modify stuff in a table. You can stop them doing this with a form.

 

[David8]

Well, I've now amalgamated all of my fields from the various old tables,such as the old PC table; Monitors table; Printers table; Switches table etc.

There is now just one big overall equipment table that covers all the types of equipment, and includes a new Type field that specifies what kind of equipment an item is. There are a little over 80 fields, which is not as bad as I was expecting. Even so we clearly won't be using an 80 field table for data entry, EVEN IF we still thought entering data in tables was a good idea (and I can see why you are saying it isn't).

Thank you Dave (G-t-H) for explanation about validation. That is definitely something for me to think about. However, I don't see the need to nanny or restrict my users too much in entering data. Silly entries haven't been a problem in the past, and the database users all maintain the equipment and are familiar with it.

You didn't really say explain your earlier point about what was wrong with having lookup fields embedded in tables.

Anyway I've made an important realisation. That you can enter and ammend data through queries! I know you won't be recommending that, but for me this was a eureka moment (however weird that may seem to you).

I'd just assumed queries were fairly 'dead', kind of like reports but just capable of being sorted and having the columns moved around without having to run it again. I'd never previously tried entering data in one, and had no idea that it would feed straight back and update the table.

I'll illustrate in my next post (on the next page) how I might now answer my first post in this thread.

 

[David8]

Re: Question about equipment inventory database design

In my first post I asked:

But lookup tables and relationships do not solve all my problems. I NEED the database to 'understand' that there are not printer serial numbers and monitor serial numbers, there are just serial numbers and it is a common field throughout the database. BUT when a user enters a new serial number in the Printers table, I do not want them to have to enter it in a Serial Numbers table first, then enter the serial number again in the Printers table. I only want them to enter s/n once.

My answer now would be:

Simple. Collect all the data into one equipment table (as Bob said). It will have only one field for serial number. That will 'logically unify' the serial number field (and other fields like it) straight away. [EDIT: no I meant to say all equipment rather than data, Bob rightly picks me on this below. My use of the word Data needs to sharpen up]

Then create a Printers query based on the All Equipment Table. If I design this query correctly it will look exactly like the old Printers table. Enter a new printer and serial number through this query and it will update the underlying table.

...when a user enters a new serial number in the Printers table, I do not want them to have to enter it in a Serial Numbers table first, then enter the serial number again in the Printers table. I only want them to enter s/n once.

What was I going on about? Well unfortunately TO SOME EXTENT I was using tables for what queries are meant to do (bring together similar types of data and subject them to certain criteria). And what was I using for what tables are meant to do?

Answer: through a fuzzy understanding of relationships I was attempting to get the logical structure right by 'relating' (eg) Manufacturer fields in different tables, by linking them up to a common Manufacturers lookup table. Clearly this was crazy and unworkable with a potentially unlimited lookup table like 'serial numbers' and hence my confusion and puzzlement: how was I to 'logically unify' serial numbers, without relating them back to a serial numbers lookup table?...I was asking.

The short answer was... "By using a table properly to unify serial numbers, one table for everything. And start using QUERIES for what you're trying to do with tables."

Does this help anyone see the misconceptions I was suffering from?

[Edit: obviously way too tired when I wrote this. I didn't mean one table for everything. I meant one table for every item of equipment as opposed to different tables for diffent types of equipment]

 

[David8]

Earlier in this thread, I said:

I do not want the person entering data for printers to be faced with long rows of computer or monitor specific fields that are irrelevant to printers. But I do want a data entry method that once a printer is indicated, all the relevant fields are 'shoved in the face' of whoever's entering data. I do not want them to fail to consider a relevant field.

Entering printer data in the bottom row of a Printers Query achieves exactly this. So long as the Printers query includes all fields relevant to printers and no others.

Of course it would still be nice to do it with forms, and have the form adapt to the Equipment type being entered. Data ammendment might still be best through the query.

 

[David8]

Re: Question about equipment inventory database design

So, lookup tables are not crazy as you would specify. But you apparently did not learn anything from the samples I provided.

No I was talking exlusively about my old misconceptions about the proper roles of tables and queries and relationships.

I will DEFINITELY still be using lookup tables. But I will no longer be labouring under the misconception that by relating a Printers:Manufacturers field and Monitors:Manufacturers field back to a Manufacturers lookup that I will somehow be logically unifying the fields into one Manufacturers field.

Put another way, I had in the past been putting arrangement of the data for easy consumption first, and getting the underlying logic right last. A lot is making more sense as I now [try to think about] do[ing] those two operations the other way around.

I will still be using a manufacturers lookup table though (for example).

I better go and re-read on normalisation, to make sure I have grasped the concept.

 

[David8]

Re: Question about equipment inventory database design

This is the part that I'm wondering about:


I don't remember saying that all will go into one table. The second example I uploaded should have made that clear. There is an Equipment table, yes. But it only has fields which are common across ALL equipment.

Yes sloppy explanation on my part. All the EQUIPMENT will go into one table is indeed what I meant. That is an obvious step for you, but sadly hadn't been for me, because as I said I had been warping my use of tables. I had kind of created "pre-queried" tables. They are now scrapped and replaced with real queries.

Then there are the attributes which you can add more easily enough. And then there is the combination (or junction table) which stores the ID of the equipment with the ID of the attribute and the attribute value.

Thank you I will think about whether I have got this right.

Also, I would not use Serial Number as the primary key. Just use an autonumber and let Access manage that part.

I never have used the serial numbers as IDs. I have used auto-generated IDs in the past, and still am. But we are recording all the serial numbers. This is a detailed inventory. If we ring up a printer service centre and they ask the serial number, we want to know straight away.

Perhaps I have failed to explain the exact nature of my past confusion. Nevermind. That isn't important. I should concentrate on greater clarity looking forward not back.

 

[accessfleet]

It is interesting to read all the post on this subject. I have been using an access2007 database for about seven months now to track Vehicles, Vendors, Parts, Insurance, Fuel and work-orders. I am currently working on the reports.

You might guess that a number of look up tables are in use. Mechanic's are not good typist and generally lack spelling skills. So tables to select Make, Model, Department, fuel type and vendor were natural look-up fields for the UNITS table.

Fields like Vendor, Department and labor code made natural lookups for the PARTS table.

unit#, Department, Work type, labor code, part# made look up fields for work order tables.

The way they can be scrolling down and selecting the right data is cumbersome but a few alpha or numeric characters greatly speed up the search. 2 or 3 key strokes to select a lengthy name or number entry .

A check box is utilized to close a work order. But a lot of things happen when the field is checked. current mileage is posted to the mileage field in the UNITS table, The quantity used is subtracted from the quantity on hand field in the PARTS table.

Now to the point, the end user enters and updates fields like VIN number from a form. This allows user to see at a glance when a field is (null) it also makes it possible to prevent data from being deleted in error. deletions have to be done at the table level and their access to those tables can be limited.

Good luck with your DB.

 

[gemma-the-husky]

david8

lookup fields in tables arent the end of the world, but they disguise the real data that is stored in the table. Often the lookup combobox that it creates automatically on a form isnt correct automatically, and it only takes a few seonds to create one manually anyway. Also the lookup field may interfere with searching and sorting.

The presence of a lookup field is also anti-relational really, as it confuses form and function if you like. The data structure ought to be distinct from its usage.

Since you shouldnt be entering data directly into tables (or queries for eaxactly the same reasons!) the downside of using table level lookups outweights the dubious benefits

MS seem to be moving towards a number of (undesirable?) features though, that build on this - eg - the plus sign you may see in tables, which exapnds a record to a linked sub-table is rather pointless for the same reason (you shouldnt be using tables directly), and consumes system resources. Probably an attempt to try and make Access more easy to use. I am not sure it does.

 

[David8]

We had confused ourselves over the issues of recording items of equipment of different types, which had some common attributes but many attributes that only applied to one or two equipment types. Of course we now know that the common attributes mean those items of equipment must be recorded in the same table. The "messiness" created by the "item type specific" attributes is irrelevant since you shouldn't be using a table for the entry, ammendment or review of data (as we were). I suppose we'd come straight from Excel and failed to engage with forms and queries properly.

While we got that wrong, we did at least make fairly extensive use of lookup tables and combo boxes. Manufacturer, room (location), screen size, screen aspect, Intel or AMD, colour or mono, inkjet or laser, lcd or dlp, tft or crt, interface (eg serial, USB, parallel, ethernet) and many more. These things can generally be limited to 2-20 common choices.

It is important to standardise the data entered where appropriate. If you don't you will mess up your ability to run effective filtered queries on the database later. eg a Canon printer won't show up in a Canon based query if someone accidentally entered it as Cnon.

There does come a bit of a problem with something like Model Name. When you have a very large number of computer monitors purchased over several years new models are constantly appearing and it can be a pain to keep entering a new option in a lookup table before entering a new monitor. However, the benefits of being able to query a particular Model of something and being sure you are getting all relevant answers is most important.

We may have made a mistake with the Interface field where you can tick multiple boxes in the same field and this outputs a list seperated by commas. Maybe Ethernet (true/false), USB (true/false) etc should all be seperate fields.

I agree with Gemma the Husky, I'm not convinced by sub-tables, we don't use them, but maybe I'm missing something.

 

[accessfleet]

I have sub tables in two tables Vendors and parts. The purpose of them is to track reference data.

Vendors sub table is used to log invoices what have been processed for payment. Needed because our accounts payable is in another location. Vendors call me when they have an invoice that hasn't been paid. I can quickly check when/if I processed it and sent it down town.

Parts sub table tracks each use to the unit#, Workorder#, or employee who checked it out of inventory.

Neither use is high powered but very handy to have invoiced data "pre sorted" by vendor form for entry and reference. It is also helpful to know how many different units use the same part number. Increase/reduce inventory decisions etc.

BTW thanks for all the thought provoking post of the different forums.

 

[odingalt]

Thanks for the insightful thread. Let's just say I have an equipment/asset tracking database, but because of the way the tables are structured (different table for each equipment type), it's become difficult to manage and certain features are much more difficult to implement (such as tracking asset location history).

However after following your example database, under the tblPreferenceDetail, it seems that the PreferenceDetail column data type is set to "short text". Am I to understand that all of my equipment preferences must be of type "short text"?

I don't see any way to implement other data types. For example if one of my preferences is a purchase date, the purchase date must be stored as short text (because all preference details live within the PeeferenceDetail column of the tblPreferenceDetail table). Since it's stored as short text (and not a date) then I can't use the date picker? And thus I will not have integrity during data entry, I would have to write a bunch of VB script in order to enforce legitimate dates? And later to retrieve the date do a special conversion from short text to date? etc. etc.?

Rinse and repeat this example for numbers, text, dates, etc. There's no denying that it's useful and time saving to store a date as type date, text as type text, number as type number, etc. But my simple mind can't figure out how that's possible in the example database.