Ms-access – Distinguishing Records with Related Fields

database-designms accessms-access-2010

I'm a newer user (obviously) and would greatly appreciate question writing suggestions as well as answer suggestions:

I have a lot of interrelated tables which largely depend on my Products table, which is the master list of a certain product. Much of the information for any given product is determined by traits from the Shipments table. There is a one-to-many relationship between Shipments and Products. Each shipment has a Product Version field, but in addition to having a unique version, any shipment may be a trial shipment.

Which is the better design?

There are two workable designs which come to mind.

1. Current Design

The Product Version field contains values for each possibility. For example, a product of version 7.6 would have the value 7.6, but a 7.6 product which is a trial would be 7.61. To select all 7.6 products, I would use a Like "7.6*" statement. This keeps all of the versions in one table and I have this documented for the next user. The irritating thing is that I have to have a field containing the necessary SQL to query that specific version. This SQL is located in a ProductVersion table.

2. Adding a Field

I could have a True/False field for each shipment which indicated if it was a trial… (I also have to take into account the ease with which new users will be able to use this without me, but as that's a bit subjective, that's not the core question.)

I don't know if this makes a difference but

Part of the fun with my current setup is that only some records in Products have associated Shipments, since for sometime the shipments were not recorded. The shipments are also imported into the database in a different process, sometimes after the associated product.

So:
Is it a better design to add a boolean field for trial products?

Best Answer

I encourage those more knowledgeable than I to edit this answer or question with additional information if it would be beneficial to those reading this in the future.

After experimentation, I've implemented Option 2, the boolean field for trial version.

I think this solution is

  • better normalized and
  • more clear to users

when each attribute is broken down into its own field (rather than having a version field which has to describe multiple attributes like product version and whether or not it is trial).

In some way it could be beneficial if I could just add new version numbers like 7.622 then 7.6221 to get more specific, but this can be confusing to decipher and is also somewhat arbitrary. The alternative--breaking the version/trial into multiple fields--also discourages others from creating new potentially duplicate or unnecessary version numbers which make things harder to find/query.

These additional fields, such as the trial boolean, are related to each Product through the one-to-many relationship between Products and Shipments.

Related Question