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