Table Structure

[nestoc]

Ok this is a long table design question I have been having trouble wrapping my head around.

I have a database my company uses to keep track of equipment that we repair. Currently we have a table 'repairs', with fields like:
ID, customer, equip_type, serial_num, date_received, date_estimate_done, date_estimate_sent, date_PO, date_shipped.

We use all the date fields to keep track of the status of the repair, for example if there is a value in date_received, and date_estimate_done, but date_estimate_sent is null, we know we need to send that estimate to the customer. This has all been working fine for us for a while now.

We occasionally have to send a piece of equipment out to a third party for repair, and what I want to do now is add the ability for the database to keep track of this.

My first thought was to add another date field to the repairs table, say date_senttothirdparty. But we also need to keep track of the vendor, so I would need a vendor field as well, that would only be used when equip was sent out to a third party. Also maybe there is some remote chance that a machine might have to go out twice to two different vendors.

So my second thought is to take all the date tracking out of the main table, and have two tables, something like this:
repairs: ID, customer, equip_type, serial_num
status_log: ID, repairID, date, event, vendor

Then we put things like "Received", "EstimateDone", etc. in the event column. and the vendor column is still only used for the one event, "SentToThirdParty".

The problem with this is it was very easy for me before to have controls on a form editRepair that is bound directly to fields like date_received and date_shipped. And we can then just fill in the received date and shipped date on the form. Now I am not sure how to accomplish the same thing so easily. I would have to maybe have a button that when pressed runs an insert query against the status_log table? I can't figure out the best way to do it. Anybody have any advice?

 

[spikepl]

her eis my take on this (I am sure you'll get many other):

Do not mix information types. You have a reasonable recording of status. In my reading the essential bit for your work process is how far in your process is the equipment, and that is what your record shows. Your first proposal was to record date for SentToThirdParty and I happen to think that is a good idea. The remainder of the story are potentially repetitive details (which third party, how many times on whcih date etc etc ) not relevant to the main work flow, which just (or still) shows that the equipment is out of the house. So my solution would be a hybrid of your two: maintain your current structure, add and record the date (to show that the equipment is off to third party) but then have a separate table linked to the current record, in which you record each vendor and date ad libitum.

 

[nestoc]

Thanks for your response. I had considered something like that too. But the scenario where a machine is received -> sent out -> returned -> sent out -> returned -> estimate completed is still throwing me off.

If I only have the one additional date field in the repairs table I would have to overwrite it the second time, and then I would not have a complete record of the equipment's history.

If I have the date in the repairs table, and an additional table with it's own date field like you suggested, and track the date every time it goes out, wouldn't that make the date in the repairs table redundant?

I suppose another option would be to leave the repairs table exactly as it is, and keep another table for machines that are sent to outside vendors. But then I would have machine status information in two different tables. And to get a clear picture of where the machine is at in our process I would have to query both of the tables, and keep in mind the order of the dates in the second table.

I feel like there has to be a more elegant solution to this, but I just can't think of how else it could be done.

 

[pbaldy]

FWIW, I liked the "second thought". It "feels" like the normalized approach, and seems like it would give you the best history of what happens for a repair.

 

[CJ_London]

I suppose another option would be to leave the repairs table exactly as it is, and keep another table for machines that are sent to outside vendors. But then I would have machine status information in two different tables. And to get a clear picture of where the machine is at in our process I would have to query both of the tables, and keep in mind the order of the dates in the second table.

I do think this is the way to go. I would have a table which has fields like this

TblID autonumber PK
MachineID Long FK
Action Text (returned/sent out/etc)
ActionDate Date

ActionText could be entered via a combo box and you could filter the rowsource so that you can't return without sending for example.

You can easily determine the current status by looking up the record with the latest date

 

[Cronk]

Obviously, table structure will depend on your business model.

Do you want to keep track of previous times a piece of equipment item has been repaired? Can the equipment go to more than one outside repairer? Can the item be sent to an outside repairer more than once?

I'd be having one table for Companies. This would provide company details for both client and outside repairer. There'll be a table for equipment being repaired. And other tables depending on answers to previous para, now or possible in the future.