Ms-access – Additional info on some rows in the table

database-designms accesssubtypes

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

Best Answer

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.

Related Solutions

How should I best design the tables and relationships, given the following rules

From what I can understand, apart from a Customer table, you'll need three tables:

Contract — holding the single-valued data about a contract, excluding receipts. It might record the latest receipt number for the contract, but that would be an optimization, storing derivable data. Primary Key: Contract Number (aka Lot Number).
Contract Items — holding the 1-6 items for the contract. Primary Key: Lot Number, Lot Sequence Number. Lot Number is a Foreign Key reference to Contract.
Receipts — holding information about receipts. Primary Key: Receipt Number. Foreign Key: Contract Number reference to Contract again.

A given receipt is associated with one contract; a single contract may have multiple receipts over time if it is a pawn contract (buy and sell contracts will have a single receipt).

Even if a new receipt is not issued when a pawn contract is cancelled, there'll need to be a database update of the contract or the receipt (or both) to indicate that the contract is cancelled.

Is there anything that I'm missing here?

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

Categories and Buckets.

create table categories
(
  category_id int primary key
);

create table buckets
(
  bucket_id int primary key
);

Depending on how to interpret "an item may belong to at most 1 bucket" you need to do some different things with Items.

An item has to belong to one bucket.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int not null references buckets(bucket_id),
  unique (category_id, bucket_id)
);

If an item may belong to one bucket but does not have to you need to allow for null values in bucket_id and use a filtered index as the unique constraint.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int references buckets(bucket_id)
);

create unique index uq_items on items(category_id, bucket_id) where bucket_id is not null;

There is a collection of items where each item belongs to 1 category.

category_id as a foreign key in items.

an item may belong to at most 1 bucket.

bucket_id as a foreign key in items.

There is a collection of buckets which may contain at most 1 item from each category.

A unique constraint on bucket_id and category_id in items.

Best Answer

Related Solutions

How should I best design the tables and relationships, given the following rules

Sql-server – How to model and enforce constraints on a categorical item contained in a collection

Related Question