Related field or calc?

[nscheffey]

Hello-

 

I have an inventory management system that tracks where in the warehouse a product is. Every day all the inventory is barcode scanned, generating a Scan record that stores the inventory ID and the location ID. The relationship between Inventory and Scan is sorted by time, and there is an unstored calc in the Inventory record that returns the most recent location. There is also a stored auto enter calc referencing the unstored one, that updates based on a Let trigger. The stored field is used for finds and sorting.

 

My question: is this a dumb way to do this? From a normalization point of view it seems to me that the "most recent location" data should only live in the Scan record, and not the Inventory record. Then we just show the related field on the Inventory layouts. This would get rid of all these dumb trigger fields (which were formerly even worse lookup fields.) But won't we see a performance hit when finding or sorting on the related field? E.g. "show me all products at this location"? 

 

How would you guys do this?

 

-Nate

[mr_vodka]

What kind of volume are we talking about here? How many records in your table? When sorting and finding are you only looking at items that havent been sold? If so what is that record volume?

[David Jondreau]

Sounds like a reasonable solution. I do similar stuff all the time.

 

Normalization is great and all, but in the end, you've got to make the database actually work. And finding and sorting on related fields is a performance weakness of Filemaker.

[nscheffey]

Total Inventory: 280k records

Total Scans: 4.7 million records

Active Inventory: ~4000 records

 

Mr Vodka: no, the finds are not optimized to only search on active records. We definitely need to do that. However, the warehouse is connecting via WAN so I'm worried that even a find on 4k related fields might be too slow. 

 

David, you are right, an actual working system trumps any database theory. But this Let trigger method feels a bit flakey and untrustworthy, and doesn't seem to always update the auto enter calc. Maybe because we are using modification timestamp as the trigger field? Should it be the unstored calc?

 

Thanks,

Nate

[David Jondreau]

You could tweak the trigger set up a little. You don't need the unstored calc at all, the triggered auto-enter could just reference the related field.

 

I don't know what that unstored calculation actually is, but finding/sorting/etc on related fields or unstored calcs can really depend on the calculation. Aggregate functions, like Sum() are brutally slow, but others, like Last() (or just using the related field) can be ok.

 

Another option, if you have Server, is to do a scripted update. The data won't be as fresh, but it can save you some trouble.

[nscheffey]

Thanks David. So, if my auto-enter trigger looks like this:

 

Let(

trigger = modfication_timestamp;

related::field

)

 

what can I use for the trigger? The modification timestamp doesn't fire when the related field changes, and I don't think the unstored calc can work as a trigger field either, right? 

[David Jondreau]

You need a "tickle" field. I use a timestamp field, called Trigger. Reference that in your calc and set that to Get ( CurrentTimestamp ) after doing your scan. You can have a script trigger on the scan field or have a Server side script run.