Additional info on some rows in the table

Question

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?).

Answer 1

It sounds like your categories are a case of the subtypes pattern. Accordingly, I've added the tag subtypes 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.