Inventory Database Design Question

[RoaringChicken]

Hi.
Vista Ultimate
Access 2007

I'm developing an inventory database and have question on design. The database stores collection details. One item in the collection, one record. The design question is about showing related items. For example:
I have a typewriter Model 1
I have a manual for typewriter Model 1
I have a manual for typewriter Model 2 but no typewriter model 2.

What I would like is that when the record for typewriter Model 1 is displayed the user can see that a related item is the manual for this typewriter or whatever other items in the collection relate to this. Should they pull up the record for the manual they see the typewriter itself is a related item and again any other items in the collection. Likewise should I get a typwriter Model 2 it gets entered into the database linked to the typewriter model 2 manual so that when either record is dispayed the other related item is showing on the form somewhere.

How can I link the related records even though they are in the one table. I think I need a separate table but I need some guidance on design for this and how it might be done via a form.

Your advice is greatly appreciated.

Regards
Shane

[jimatqsi]

You probably need to spend some time to figure out how you would do it with pencil and paper before you can go far with the DB design. Yes, you probably do need another table, with a one-to-many relationship to the inventory table (i.e. many records in this new table relate to one record in the inventory table, but I suppose it could also be the reverse)

What if you have a ribbon, or a power cord, that fits both models? Inventory is rarely as simple as simply "model 1" and "model 2", there are often variations and complex relationships among models and parts.

How well you know the inventory data will be important. If you've worked in the industry of this firm for a long time you probably know the multiple variations that you'll face, or the lack thereof, since some inventory systems are more simple than others.

If I were to give a concrete answer to your question, I would say to make a new table with three columns. Column 1 is the key to the table, column 2 is Part number 1 and column 3 is part number 2. Maybe you add a 4th column to describe how part number 1 relates to part number 2. A part might be a component of another part, or an accessory or even a substitute item. In the DB I am working in now, we even have "up sale" items linked to an item, to indicate higher priced items we offer to customers instead of another item.

Think ahead to what the managers are going to ask for later, build some flexibility into whatever you design.

Good luck,
Jim

[patjones]

As Jim says, sketching this out on paper is a good idea, and this will certainly become a multiple table database.

In situations like this, parent-child relationships usually make themselves apparent after some thought. For instance, typewriter manuals and ribbons by themselves don't carry much meaning; they depend upon a particular typewriter model in order for meaning to be given to their existence. So you can imagine that you might have a table that stores "parent" objects (typewriters, bicycles, etc.) and then a related table that stores "child" objects, or accessories to the parent objects (typewriter ribbons, typewriter manuals, bicycle sprockets, bicycle pedals, etc.) and have these two tables related via some sensible ID scheme.

The only issue to watch out for, as you point out, is what to do with things you put in the child table such as typewriter model 2 ribbons, without an actual typewriter model 2 in the parent table. One way that you could handle this situation is to actually put a typewriter model 2 in the parent table, with say an "In Stock" yes/no column to indicate whether you actually have this item. So, even if you don't have the item, the related record in the other table won't be "orphaned", but at the same time the yes/no column tells you whether you actually have this item or not.

Pat