0

I am trying to design an MS Access 2013 database to keep track of an inventory of items.

All items have certain common properties, like name, date they were stored, location of the physical item, etc.

The items fit into broad categories. The category is reflected in the item name.

Most items have a standardized name, made up of three letters and a positive integer. The letters indicate the category, the number is a serial number within that category. For example, PEN6' is item #6 in the "pens" category,LTR6` is item 6 in the "letters" category.

There is a small number of items which have very non-standard names like `Bob's letter to his friend'. Their category cannot be easily determined by the name, but they still belong to some definite category (recorded elsewhere).

There are some kinds of data that are only relevant for some categories. For example, "From" and "To" need to be stored for all LTR, but not for PENs. "Color" needs to be stored for pens, but is irrelevant for letters.

How can I store this additional info? I came up with two possibilities, but both have issues I couldn't resolve.

Solution #1: I make separate table for each category: An LTR table, a PEN table, etc. Each table has whatever fields are desired for that table. But when adding a new item, how do I check that the name is unique within the whole database? Because of how I chose my name format, most of the time this is already guaranteed, but for every non-standard name I would have to do a database-wide query to make sure it doesn't already exist.

Solution #2: I make a single table for all items. For each category, I project this database into a new table showing only the category I want (how?). The new table then has the particular fields of that category. When rows of this projected table are edited, the change is propagated back to the original table (if relevant, e.g. for date). When a row is added to the projected table, the corresponding row is also added to the original table (How?).

1

It sounds like your categories are a case of the subtypes pattern. Accordingly, I've added the tag to your question. There are two design patterns that you may want to look into.

The first one is called "Single Table Inheritance". This design involves collecting all the subtypes (or, if you prefer, subclasses) into one table, and leaving values missing (NULL) in places where they do not pertain. Views can be used to project this table onto subclass virtual tables. You generally need a "category" field to make the projections. It's simple, but it doesn't address all the problems.

The second one is called "Class Table inheritance". This design involves creating one table for the superclass and one table for each subclass. Attributes are placed in the appropriate table. Often, the PK for the subclass table is a copy of the PK for the superclass table. This enforces the one-to-one nature of the IS-A relationship, and provides for easy joins. Views can be used to join each subclass table with the relevant subset of the superclass table.

Your solutions #2 and #1 are somewhat like these two design patterns.

If you want to look up more detail on the web, I suggest searching for Martin Fowler's presentation of these two techniques.

  • Thank you for the answer. It does sound like the subtype is what I want, if I was coding I could easily solve this problem with an appropriate class hierarchy. However, I am trying to make a database in MS Access 2013, that is convenient for working with manually. For example, using the NULLs wouldn't work, because I would have to scroll too much to see the fields of a given row. – Superbest Nov 25 '14 at 20:01
  • The techniques I describe here apply to the design of tables for a relational database. You can apply these techniques in an ms access situation. The actual building of tables is different if you use the access GUI than if you were using SQL DDL, but the design techniques are the same. – Walter Mitty Nov 26 '14 at 1:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.