To normalize or not (1 Viewer)

Hi All -

I am working on an entirely different kind of db at the moment.

We have a special tool that we ship with some product that helps the installer with their work.

We have 20 of these tools. They are shipped out to product-specific jobsites.

I need to track them.

the DB has the "usual" array of tables for contacts, job details, etc.

Currently, I have a junction table (tblShippingDetails) that connects tblTools (tool ID) with tblJobDetails.

tblShippingDetails has fields one would suspect it would have - however - with what I have learned about normalizing and table design I find myself at a crossroads.

I currently have a field called "ShippedDate" (the date we actually shipped the tool out) and another field called "ReturnedDate" (the date we received the tool back in-house).

A complete record will have a ShippingDetailsID, JobNumber, ToolNumber, ShippingDate, ReturnedDate, FreightType, TrackingNumberOutgoing, TrackingNumberReturning, and Notes fields. That of course does not included the plethora of contacts fields in connected tables.

My question is - in your vast experience and thinking of this as a transactional db where the two events (shipping and returning) are BOTH expected to occur - does my current design seem correct OR....

....would it make more sense if tblShippingDetails had these fields: ShippingDetailsID, JobNumber, ToolNumber, Date, ShippingDirection (shipping out or returning), FreightType (UPS, Fedex, Common Carrier), TrackingNumber, and Notes?

In other words, there would be two separate records for each tool for each Job: One record would show the "shipping the tool to jobsite" event and the other shows "returning the tool to the shop" event.

The latter seems more normalized to me BUT the primary querying against the db would be date range calculations and checking to see if a tool is still out and for how long has it been out.

Does one of these designs seems a better fit than the other? I know I can accomplish what I want to do with the first/current design but I'm also sure it isn't normalized.

Thanks,

Tim

Hi Tim. Like you said, either structure should work, but one structure is more normalized than the other. To me, however, it's much easier to find how long a tool was out, for example, by doing a simple DateDiff("d",[DateOut],[DateIn]) rather than a long query trying to find the last time a tool was checked out and then find the matching check in record. Easier or not, doing it the hard way is not impossible either. It's just a matter of juggling to find out which way is more convenient or comfortable for you.

This is like the MS Lending Library template. I believe it uses a single record to track book out/in dates. It would be my preference.

theDBguy said:

Hi Tim. Like you said, either structure should work, but one structure is more normalized than the other. To me, however, it's much easier to find how long a tool was out, for example, by doing a simple DateDiff("d",[DateOut],[DateIn]) rather than a long query trying to find the last time a tool was checked out and then find the matching check in record. Easier or not, doing it the hard way is not impossible either. It's just a matter of juggling to find out which way is more convenient or comfortable for you.

Click to expand...

That's why I asked.

It seems a lot easier to me to have all the data in one record - especially for the calculations that are basically the singular purpose of this db.

Zydeceltico said:

That's why I asked.

It seems a lot easier to me to have all the data in one record - especially for the calculations that are basically the singular purpose of this db.

Click to expand...

So, I would say go for it and then move on. It will give you more time to tackle more pressing items. Nothing is stopping you though to revisit this issue later on, if you so decide to work harder, that is. Cheers!

June7 said:

This is like the MS Lending Library template. I believe it uses a single record to track book out/in dates. It would be my preference.

Click to expand...

That makes the most sense to me as well.

Thanks!

Here is a question to consider:

....would it make more sense if tblShippingDetails had these fields: ShippingDetailsID, JobNumber, ToolNumber, Date, ShippingDirection (shipping out or returning), FreightType (UPS, Fedex, Common Carrier), TrackingNumber, and Notes?

Click to expand...

Can it ever occur that the outbound shipment and inbound shipment are different carriers? If so, then you need a different way to track carriers if you use a single record with an out-date and a separate in-date.

The_Doc_Man said:

Here is a question to consider:



Can it ever occur that the outbound shipment and inbound shipment are different carriers? If so, then you need a different way to track carriers if you use a single record with an out-date and a separate in-date.

Click to expand...

Outbound can vary. Return is always UPS.

Three quick questions to see if any of them run into business logic issues;
1) Can a tool ever be sent from one work site directly to another?
2) Can more than one "Job" be at the same physical location/One Job at multiple physical locations?
3) Do they ever need to be sent back to the manufacturer from a job site for service?

If yes to any you'll probably find it easiest to have a child record for each tool that has "Current Location" which would include your shipping information along with your "date" field to show when it was arrived at that location. This means you'll track WHERE the tool is, not what job it is currently assigned to. Means you would be linking to a location table rather than "Job", and you'd also have jobs linked to locations.

Mark_ said:

Three quick questions to see if any of them run into business logic issues;
1) Can a tool ever be sent from one work site directly to another?
2) Can more than one "Job" be at the same physical location/One Job at multiple physical locations?
3) Do they ever need to be sent back to the manufacturer from a job site for service?

If yes to any you'll probably find it easiest to have a child record for each tool that has "Current Location" which would include your shipping information along with your "date" field to show when it was arrived at that location. This means you'll track WHERE the tool is, not what job it is currently assigned to. Means you would be linking to a location table rather than "Job", and you'd also have jobs linked to locations.

Click to expand...

1) possible - only because nothing is impossible - but highly unlikely as the tool needs serviced every time it has been used on a jobsite.

2) No - more than one job cannot be at the same physical location nor can one job be at more than one location.

3) We manufacture the tools ourselves in our machine shop.

Should be good on those points - but I am always grateful for any kind of logic-check!

Thank you!

Tim

Outbound can vary. Return is always UPS.

Click to expand...

Actually, that DOES make it possible to have a single record with out/in dates and only one carrier listed (the outbound carrier) as long as that "Return carrier is UPS" rule is NEVER EVER IN A GAZILLION YEARS violated.

The_Doc_Man said:

Actually, that DOES make it possible to have a single record with out/in dates and only one carrier listed (the outbound carrier) as long as that "Return carrier is UPS" rule is NEVER EVER IN A GAZILLION YEARS violated.

Click to expand...

A gazillion is a longggggg time (.....he says now doubtfully and less-confident)

Well, the point is, either you need TWO slots for shipper - out and back - or you only need one. But there is a "touch" - a really small touch - of denormalization in having an out shipper, out date, return shipper, return date in the same record. A purist would demand making a child table that would ever only have two entries per tool/job parent entry. I'm a pragmatist so will point out that if you know that your business model does not allow out-and-back more than once per tool/job record, you don't NEED the child table. BUT the more info you keep about that shipping event, the more you start to need a child table to hold all the extra "stuff" that goes with it. Like shipping cost each way, point of contact at customer site for a given shipping event (if that isn't constant for a given customer site), and anything else that could be part of the act of shipping.

Oddly enough, this tool/job table is a junction table that, if you needed to keep child records, WOULD need a PK since in that case, the shipping records would be its children.