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