Table Design Question 1

[xweyer]

I have an Inventory Database with an Items table. It tracks a pretty good number of IT items but only a handful of item types. All items have some common traits (eg Make, Model, Serial #, etc.) but each type of item may have special fields (eg drive size, processor speed for PC's - screen size, display type for monitors, etc.) Each item has a uniqe item number.

What I had planned to do was to keep all the common item information together in the Item table along with a "type" number (1 for computer, 2 for monitor, etc.) and create separate tables for extended information (xType1, xType2). The connection between the ITEM talble the xType tables being the item number field.

My ultimate goal was to have a form that would display the information from both tables when a particular item was selected so that if a computer was the item being displayed its information from both tables would appear and if a monitor was being displayed its information from both tables would appear.

I don't care if blank fields from the non relevant extended type table appear on the form too. (Which I assume to be a given) but I can't seem to get the basic idea to work in any manner through relationships or any other way.

Can somehting like this be done and if so what is the best way to do it?

 

[JeremyNYC]

Yes, it can be done. No, you don't have to put up with the blank fields. There are a couple of different ways to go about it.

Full normalization would require that, as you described, you break out non-standarx fields into separate tables, which would be related to the Item table in a one-to-one fashion. And you can definitely make this setup work, without too much effort.

On the other hand, putting all those fields into the main table and letting a bunch of them be blank is not, in fact, such a horrid thing--the fact that you would be wasting a bunch of hard drive space by having a bunch of blank fields is pretty insignificant when looking at todays computers and networks.

My suggestion for how to go about it would be to have all the fields in one table. However you store your data you'll have to do some fancy coding for forms and reports, unless you want all those blank fields to show (which I wouldn't).

One way to do this is to have separate forms for each Item type, and call the form appropriate for the item type. Here you have to be careful of parallel maintenance--if you've got five forms and you make a change, you've got to make it in five places. That sounds pretty icky.

Another way to do it is to have a bunch of controls that can go visible or invisible depending on the Item type. Pretty slick and very easy to deal with the code. But very difficult to deal with the layout, once you've got those layers of controls in place.

Another way to do it, around with which I have not played <g>, would be to use one main form with different subforms. The funny thing here is that there is a one-to-one relationship between main form and sub form. I don't think this should be a problem, though something tells me to caution you to play around with it a bit, or see if other people have used this solution, before you dive into it whole hog.

If there are no severe complications because of the relationship issues, I would say this way would be the easiest. You would simply change the conrol source of the subform according to the Item type. Now that I think about it, if you do it this way there would be no real advantage to having all of the fields in one table, so you could even keep you data normalized.

One thing that would be slick here is if you wrote a function that assembled a summary of each item into a short text string that you could display in a datasheet view of all the items lumped together. A Select Case structure that got the processor speed and OS for a PC, the screen size for a monitor, the Model Number for a printer, etc. would do the trick. That'd be mighty fine.

Hope the ramblings help.

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.