postgresql – Modeling of batch handling of other table objects?


I have a Postgres DB that has two tables, Cleanings and Inspections. They are similar but have their own methods and columns so they are in separate tables. Normally these are created one at a time and their details entered into a form. But I want users to be able to scan in (the items being cleaned or inspected have barcodes) a large batch of items, enter any common data (date of cleaning, who performed cleaning, etc.) into a form, and have the back end build all of the cleaning or inspection records in one batch automatically.

I can make a form that does this, and the back end just runs through the batch processing. But I think there would be some value in recording when batches were cleaned or inspected, and which items were processed in that batch. I’m trying to figure out the best approach. My system is Ruby on Rails, with Postgres DB. The three objects I’m currently working with are:

ppe: items that can be cleaned and inspected.

cleaning: cleaning record of a ppe item, has a ppe_id foreign key.

inspections: inspection record of a ppe item, has a ppe_id foreign key.

My idea: Single Table Inheritance; Using a table called batches that has columns:

| id | type | user_id | ppe_ids | batched_item_ids | notes |

id: primary key.

type: the two models that will inherit from this, cleaning and inspection. Rails recognizes this as the indicator of the class being referred to in the STI.

user_id: the user that created the record.

ppe_ids: an array of ids of the items being cleaning or inspected.

batched_item_ids: an array of ids of the cleaning or inspection records created from the batch.

notes: text field of notes for the batch.

Second idea: Join table, table called batches that has the columns:

| id | user_id | notes |

Same definitions as above. I will then have a table called ppes_batches like so:

ppes_batches

| id | batch_id | ppe_id | type |

id: primary key.

batch_id: foreign key of the batch referenced.

ppe_id: foreign key of the item being referenced.

type: type of record, would be cleaning or inspection. This could also work as STI. Rails has a lot of syntactic sugar from join tables. The main advantage of this is storing id’s as arrays in the DB is not always a best practice since it has to be translated in some way by Rails. Postgres has an Array column type and is recognized by Rails but I always like to consider DB agnostic solutions.

Third idea: Join tables with no STI. So the same batches table as the previous example, but two join tables:

cleanings_batches

| id | batch_id | cleaning_id |

inspections_batches
| id | batch_id | inspection_id |

These are just a primary key and two foreign keys. Rails associations allows me to take a Batch object and get all of the items in the batch through:

some_batch = Batch.first
 #returns the first batch object in the DB
some_batch.cleanings
 #returns all of the cleaning objects associated with that batch through the join table
some_batch.cleanings.joins(:ppes)
 #returns a join of the cleaning objects and their associated ppe

The main reason to even record this info is because a user might want to recall a list of items that were sent together. Other than that I don’t want to spend a huge amount of effort on this, but I know if I don’t address it, clients will probably request it. LOL