Zydeceltico
Registered User.
- Local time
- Today, 02:52
- Joined
- Dec 5, 2017
- Messages
- 843
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
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