Bryce:

It needs to track both unique items (with serial numbers), and items tracked in aggregate, but not by a serial number. For example, a jack-hammer may be tracked by serial number, but a screw-driver would not. It would only log the fact that a screwdriver(s) was assigned to something or someone. (Quantities will be tracked and deducted as needed.) The off-the-shelf software they tried could not handle these two mixed approaches very well.

Simplify by having serial numbers for everything -- and just *don't show* them for "screwdrivers" (whatever that means in reality in your system). That's one less complexity to keep track of.

Sure, you'd have to add another field -- "ShowSerialNumber" or something -- to your database, and use that to determine whether you're going to show the serial number... but that's just a tedious little bit of work; it's *simple*, as opposed to the hassle of handling objects that do have serial numbers as well as ones that don't have any.

As for the rest of it, I dunno... Where do you get all these "too many cross-references (foreign keys)" from, that you're complaining about?

Tool assignments can be to either a person, project, location, or vehicle. [...] A tool is assigned a primary warehouse [...] A person checks a tool in and out of the primary warehouse. The tool can also be sent out for repairs, inspection, maintenance, etc.

I can see this being done with only two tables, and a single foreign key relationship: Table Tools, with (among others) the column AssignedTo; and table AssignableTo, whose primary key Tools.AssignedTo refers to. Just store not only persons, projects, locations, and vehicles in AssignableTo, *but also* codes for the primary-warehouses, "out for repair", "being inspected", and "undergoing maintenance".

The person a tool is assigned to has to technically check the tool back into the tool's official warehouse before most other actions can take place. It cannot be assigned from one person to another without being checked back into the warehouse first, for example.

If you want to enforce the "has to go via primary-warehouse" rule in the database in stead of just in your application code (which, at least if you're using an SQL DBMS, is [re]commendable), you have to do two things: First, either copy the primary-warehouse entities to a separate primary-warehouse table, or add a field "is primary-warehouse" in table AssignableTo.

Then use an SQL "before-update" trigger to make sure that either the old or the new Tools.AssignedTo value is a primary-warehouse (i.e, that it exists in the primary-warehouse table, or has .T. in the "is primary-warehouse" field, depending on which way you structured the DB); if neither the existing nor the proposed new AssignedTo value is a primary-warehouse, then roll back the transaction and throw an exception -- Oops, I mean "raise an error"! :-) -- to your application and tell the user what he has to do.

HTH!

Bryce:

[Quoting me:]

>> Simplify by having serial numbers for everything -- and just *don't show* them for "screwdrivers" <<

Do you mean just "make one up"?

Fuck yeah! Rand() is your friend! :-)

The problem is that things can be assigned in quantity. For example, somebody might be assigned 16 screwdrivers, then later return or retire just 3 of them. One would have to key in 16 different assignments under your plan (or make a mass replicator util, which is kind of ugly).

Uhm... What happens to the other 13 ones? Did they get lost, or are they going to be returned later? (Possibly in drops and dribbles?) If they're "just gone", you could have a record for "screwdriverS", plural -- just add a numeric "HowMany" field, and change it as stuff gets lost. But...

I could not find a way to have a single "lump thing" stay a single lump thing throughout the life-cycle.

...no, if there are no *specific* "batches" of screwdrivers (etc), then we can't have one record for each "batch" either.

But instead I had to degenerate the model toward the "Action" entity, which divorces a record from a given physical thing.

Isn't that far too OO? :-)

But seriously, it still seems overly complex to me... How about if you think of it more as a "traditional" data model for a warehouse, which has only one record for each *kind* of thing that's stored in it, and an "amount-on-hand" field to keep track of how many of them there are?

Now here you'd have to pretend the individually-tracked items (jackhammers in your example) are "different kinds" of tools -- Jackhammer_123XB1, Jackhammer_456GX7, Jackhammer_987ZA9, etc; even if they're *actually ALL* "Black & Decker Model 14-Q" jackhammers -- and the "amount-on-hand" field for these tools is a Highlander Number: "There Can Be Only One"...

But then it'd work for bulk items, like screwdrivers: Each transaction that checks some of them out decreases the warehouse's amount-on-hand of them, and each transaction that checks some of them in increases the warehouse's amount-on-hand of them. In the case of screwdrivers, by up to however many there are, and in the case of jackhammers by at most one.

Oh, wait! Then you'd have to treat people (and vehicles, projects, etc) as warehouses too: If John has checked out 16 screwdrivers and checks in three, you must not only increase the primary-warehouse's (for those screwdrivers) amount-on-hand of them by three, but also *decrease John's* amount-on-hand of primary-warehouse-X screwdrivers by three, *leaving him* with 13 primary-warehouse-X screwdrivers on-hand. There are two records here, keeping track of screwdrivers not only in the warehouse's but also in John's possession. Mattr'a'fact, the second record must have been created when John checked the screwdrivers *out* -- sorry I forgot that in my last post.

>> I can see this being done with only two tables, and a single foreign key relationship: Table Tools, with (among others) the column AssignedTo and table AssignableTo, whose primary key Tools.AssignedTo refers to. Just store not only persons, projects, locations, and vehicles in AssignableTo, *but also* codes for the primary-warehouses, "out for repair", "being inspected", and "undergoing maintenance". <<

I was indeed thinking about something like that where the "out for repair" would indeed be a location instead of a status-like code. But, I see at least two problems with it. First of all, I don't think the "reviewers" here would go for that kind of approach (I did not ask, but it is a hunch based on the way they model their tables here).

Uuh... Ask them gently why they're hiring you to do it in the first place -- if they think they're better at deciding that kind of thing than you are, then why aren't they writing the app themselves?

Second, the primary-warehouse has to pretty much remain stable. Thus, if it goes out for repair, it has to come back to the *same* warehouse. (Transferring to a different warehouse {home} should be an explicit transaction.) Thus, "home" needs to be a permanent field instead of a transient location IMO. I suppose there could be one location called "home", and let the HomeLocation field tell the user where that is. However, I don't see a significant advantage of that.

Well, I suppose there'd be *different* "home" locations -- but yeah, just add a HomeLocation foreign-key field to the Tools table, that refers each (kind of) tool to one of the primary warehouses.

Also, I think each of these should remain a separate entity.

Sorry, lost you there -- each of what?

Yeah, if you go with the "traditional warehouse" data model but still want to keep track of each warehouse's allotment of otherwise identical screwdrivers, you'd have to have "fake kinds" of them: "Warehouse 1 screwdrivers", "Warehouse 2 screwdrivers", ... But that's really no worse than the individually-tracked jackhammers.

For one, they may contain entity-specific information for user's information (but not that the tool cares about). I suppose I could use a cross-reference table, but I don't think that is needed because they are generally one-to-one relationships. A given tool or action will only need to reference one location or one project or one vehicle, etc.

Uuh... yes, that's why there's only one "AssignedTo" field for each record in the Tools table. That's what you update when a tool gets checked in or out; no more tables are needed.

Thanks for your suggestions.

You're welcome.

Many resembled some of my earlier approaches.

Heh... Guess you've had more time than I to think about it too, eh? :-)

Perhaps I should not give up on those approaches so easily just because I found a few hitches. For example, I could perhaps simply split a tool record into two+ records when needed. It is perhaps a tradeoff between possible complex fudges to simple abstractions, or going with complex abstractions from the start.

No, I don't think you should... KISS and all that; if you have a straight-forward foundation it may actually be easier to build something sophisticated on top of it, than if you have an overly-complex Byzantine one. And yeah, of course you'll have to split tool records in two lots of the time -- but hey, I don't think that's a "complex fudge" at all! Bog-standard for this kind of application, I'd say.

The rule is simple: Whenever some of the amount-on-hand gets left behind (i.e, less than all of a warehouse's "batch" tools are checked out, or you check in fewer of them than you've checked out), a record may need to be split -- a new record is created to tell that John now has 16 screwdrivers, and the amount-on-hand of screwdrivers in the warehouse he checks them out from simultaneously decreases by sixteen. When he checks them in, if it's all of them, erase the John-screwdrivers record (unless you want to copy it to a history table or something?) and just increase the warehouse amount.

Don't worry about treating the Highlander tools differently -- just go by the numbers, and they'll be treated right automagically.

HTH!