CODEWITHSUNDEEP
phpmysqlinventory
user781439
user781439

Reputation: 257

Inventory System - Proper way to keep track of status and location of parts

If I have a table called group:

[Part_one_id|Part_two_id|Part_three_id|status|location]
     1            7           6        ready     long/lat
     2            2           9        in-use    long/lat

And there are three separate part tables for each part:

     Part one                     Part two                   Part three
[id][measurement]             [id][pressure]               [id][temperature]

Which way should I track location and status?

  1. Any time the status updates on the group, run another query to update the status and location of the parts in their own tables
  2. If I need to find out where a location is I look at what group it is in and pull the current location

Upvotes: 1

Views: 490

Answers (1)

hsanders
hsanders

Reputation: 1898

There are a few big issues I see with your table structure off the bat.

  1. You should never have a table with something like: part1_id, part2_id,... Because if you want to add more parts to a row than the original design you need to add columns, with a large table this is untractable. Instead use a many to many relationship with a join table.
  2. You should probably audit locations so you have snapshots of where it is at any given point in time. That means you should have an audit table that has part_id, location_id, created, modified and a location table that has lat/long.

As far as the answer to the question itself. I don't particularly like MySQL triggers because the syntax isn't that great and they don't do as much as they do in other databases like PostgreSQL or Oracle. That being said, the appealing thing about triggers in this instance would be that the trigger would execute in the same transaction as the update/insert that triggers it, so you would get that business rule for free. However, depending on what you use to access your database you can just start a transaction and not end it until you've done your auditing inserts.

Ultimately what your tables would change to look like would be:

group: id, name, created, modified, status

part_group: part_id, group_id, created, modified

location: id, lat, long

group_location: group_id, location_id, created, modified

Then the most recently created group_location would point to your current location for a given group.

Upvotes: 1

Related Questions