Re: => Trigger to split Trailer Loads

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 04/19/04 

Date: Mon, 19 Apr 2004 23:24:51 +0200

Hi Rhonda,

It took a little longer than I expected, but I didn't forget you <g>.

I think I can help you take this hurdle, but I will not just answer
your original question - I want to help you remove some fundamental
flaws from your table design first. Database projects are just like
building projects - if the foundation is wrong, no builder will ever
be able to construct a building that will last.

Your post already answered a lot of my questions, but there are still
some grey areas that I need to clarify before I dare propose an
alternative table structure.

>>Is this "half" to be taken literally (ie 26/2=13 loads at each
>>temperature), or can one "half" consist of more loads than the other
>>"half"?
>
>I had to check on this one. I am told that the partition in the Trailer is moveable and that the rear of the
>trailer may have up to a maximum of 22 and the front 4 or various other combinations, maybe the front
>of 20 loads and the back of 6. I am not sure about the implications of this in my db designs I am trying
>to solve the small issues first.

I think your original design already covered that.

>Consolidation took me rather some time to understand myself.
>The company I work for want the full benefit of loads leaving the yard. If a small load of apples come from a supplier
>in England and need to go to Scotland and another small load of bananas come from a different supplier in England
>and need to go to the same supermarket in Scotland it is more cost effective to bring both the apples and bananas
>back to Turners and put them onto one trailer to go to Scotland. This is called consolidation.

Okay, that makes sense. It's what I would do if I owned a transport
company <g>. But I would also do the reverse: if two different
supermarkets in Scottland each order a small load of apples and both
orders come from the same supplier, I'd put combine orders into just
one trailer as well. And the next step, of course, would be to combine
the apples from the England supplier to the Scottland supermarket with
the bananas from a different England supplier to a different Scottland
supermarket. I hope this sentance manages to put across what I mean.

Question: Does Turners use all these strategies to combine multiple
small orders into one trailer? From your previous posts, I get the
idea that only the multiple-suppliers-to-single-supermarket strategy
is used, not the single-supplier-to-multiple-supermarktes strategy or
the multiple-suppliers-to-multiple-supermarktes strategy.

Question: Are at most two different orders consolidated into one
trailer or can the number of consolidated orders be more? My common
sense thinks it may be more, but your previous posts don't confirm
this. And there may of course be physical, legal, contractual or other
limitations on the maximum numbers of orders that may be consolidated.

>-----------------------------------------------------------------------------------------------------------------------------------
>>I'd leave out this table. Just put the supermarket as varchar(50) in
>>all tables where you now have supermarketID. That will save you a join
>>for each report.
>
>>The same goes for the Suppliers table.
>
>I did think about this however Supermarket and Depot form a Many-to-Many relationship so I put a joining table
>called Delivery to make a Many-to-one and one-to-many.
> => Asda a supermarket is located in Bedford, Dartford, Lutterworth and other depots
> => A depot like Lutterworth is a place where more than one supermarket may reside, there may be an Asda and
> Sainsbury located at Lutterworth.
>
>I thought the same about the Suppliers and locations and added a joining table of Collection, as produce is collected
>from the Supplier and Delivered to the Supermarket.
> => HartWW is a supplier (that grows apples for Asda a supermarket) and is located in both Ledbury, a place
> name, and Sheerness, another place name or location
> => Exning is a place name or location where both AMS, a supplier, and Lighthorne, another supplier reside.

This triggers some questions on supermarkets and suppliers.

* Does Turners do business with multiple supermarkets/suppliers that
have the same name? If so, how do the people on the work floor
distinguish between the two Asda's?
* Do you store any information on supermarkets/suppliers other than
the name and the artificial ID? If not, is there maybe other
information on the supermarkets/suppliers stored in other systems? Do
those systems use relational databases as well?

And some questions on depots and locations.

* Are there any places that are used as depot as well as location? (In
other words: are there any places where goods from one or more
supplier are collected by Turners, but where goods from other
suppliers can be delivered to one or more supermarkets as well?
* If yes, what proportion of the total number of depots and locations
does this apply to? Are they rare exceptions or rather the rule?
* Are the answers to the above questions prone to change in the (near
and not-so-near) future?

>------------------------------------------------------------------------------------------------------------------------------------
>>What data goes into the columns temperature and tempType? I'm rather
>>astounded by this table. I would store temperatures as int (or, if
>>extra precision is needed, decimal or even float), not varchar. Not to
>>mention using *two* varchar columns!
>
>>But then - maybe I'm now showing my ignaorance of the transportation
>>sector.
>
>Not at all, I agree it looks strange without knowing what data the table holds
> ID temperature tempType
> 1 +3 Produce
> 2 +5 Produce
> 3 +10 Produce
> 4 0 Chill
> 5 +1 Chill
> 6 -0 Frozen
>
>Actually, I had to just check this and have now updated my table :) These are the different temperatures at which
>produce can be transported, different for frozen peas than vegies and meat.

Is there any industry-standard notation for temperatures, that will be
recognised by most of your customers (supermarkets as well as
suppliers)? If so, see if you can use that. Speaking the same language
as your customers always helps a lot!

Other than that, I'll leave the Temperatures table alone for now, as I
still don't quite understand it. If my math teacher saw -0 listed as
different from 0, he'd probably have an attaque right away!

>>Is the vehicle always stationed at the company depot where the regular
>>driver is assigned? If yes, get rid of the depotID column in this
>>table, unless performance tests show you really really really need
>>this denormalization.
>
>I wasn't really sure what to do here, I just thought it looked neater and I thought that as a Vehicle seemed to move
>around and be assigned to that particular Turners depot that I would just put it in a different table. However I not
>sure it really needs to be.

<rant>
Choosing a table structure because it looks neater may result in some
very good designs. Just as betting 10,000 pounds on the race track may
result in great wealth.
The only proper way to design a database is to make sure you know
exactly what kind of information is relevant to the future users of
the database.
Talk to the people that conduct the day-to-day business. Talking with
management is fine, but the only people that can really give you the
details are the men and women on the workfloor. Take time to talk with
them. Spend a copuple of hours on the floor, just watching what
they're doing. Ask what they do, ask why they do it. It's even better
if you can participate in what they do.
</rant>

>Turners as a company exists in Scotland, and quite a few places in England. The Vehicle while stationed in Scotland
>will have a Depot of Scotland assigned to it when it is sitting in Exning it is assigned the depot of Exning, however
>all the Vehicles belong to one main yard and that is Exning.

You lost me here. If all vehicles always belong to Exning, than
there's no need to store that in the database. If the depot a vehicle
is assigned to is based on it's whereabouts, then the Vehicles table
might not be the best place to store that information. If transporting
goods from Englnad to Scottland and back again is your business, you
can expect the depot of vehicles to change daily.

Do you need this information for the application you're designing? If
not, get rid of it. If you do, try to find the best way to store the
relevant information without having to update this by hand on a daily
basis. Again - talk to the people on the floor. They've done what
you're trying to automate for years already, so they can tell you how
they handle this.

>-------------------------------------------------------------------------------------------------------------------------------------------
>>The column name "regularDriverID" in the Vehicles table suggests that
>>vehicles are sometimes assigned to another driver. Should there not be
>>a column DriverID in this table?
>
>Woops, the regularDriverID is really just the DriverID, should have just called it that.

You're sure?

That would mean that a vehicle is unused if a driver is ill. And that
a driver has to take a day off if his/her vehicle is in repair. Ask
the planning department for confirmation on this.

>-------------------------------------------------------------------------------------------------------------------------------------------
>>I'd suggest removing these tables completely. Just add numOfLoadsRear
>>and numOfLoadFront to tblConsolidate (normalization purists will be
>>all over me for this, but I prefer a pragamtic approach - I wouldn't
>>have suggested this if trailers could be divided in more than just two
>>compartments).
>
>This is very important that I get this part of the db schema correct and in fact this is where I have spent most of
>my time as it is the biggest part of the project.

>From the looks of it, I agree that this is a crucial part of your
project to get right.

>To explain I will show you some example data that you might give me any suggestions should I have missed
>something.

(snip example data. Unfortunately, the tables didn't format quite
right. Not in a nonproportional font, neither in proportional font.
But I think I could extract the gist of it.

A summary of what I think are the prerequisites, pending your answers
to the questions above:

1. If an order is for more than 26 units, a number of trailers will be
loaded with the maximum of 26 units for that order. The remaining
units (if any) are combined with other orders to maximize trailer
usage, if possible.
2. If an order is for less than 26 units, it will be transported in
just one trailer. If possible, the order will be combined other small
orders and/or with the remainders of large orders.
3. Orders can only be combined if they need the same transport
temperature.
4. Exception to #3: some trailers can be split into two distinct
temperature zones. They can be used to transport any number of orders
with at most two distinct temperature requirements.
5. Twin trailers can set the temperature in both zones equal. That
way, they can be used as if they were single containers.
6. All containers hold a maximum of 26 units. If twin trailers are
used with different temperature settings, the total number of units in
both zones combined may not exceed 26. There may or may not be a
minimum and maximum number of units per zone - I suggest you ask for
confirmation on this.
7. There is no limit to the number of orders that are combined into
one trailer as long as the storage capacity is not exceeded (thus, the
theoretic maximum is 26 orders of 1 unit each).

What I think you need to get your project in full swing is:

A. The relational schema to hold all information, suitable for the
above prerequisites, and
B. A trigger that will split a large order into a number of 26-unit
orders (that can easily be assigned to one trailer) and if needed one
small (less than 26 unit) order for the remainder.

I assume that the process of finding possible consolidations and
deciding on the optimal consolidation will done by people, not by the
computer.
I also assume that these people are also give the opportunity to
manually overrule the automated splitting of orders, so that three
orders of 16, 17 and 18 units can be combined into one trailer with
(16+10=) 26 units and one trailer with (17+(18-10)=) 25 units.

Am I correct so far?

>-----------------------------------------------------------------------------------------------------------------------------------------
>>Each order is related to only one collection.
>Yes
>>Each consolidation is related to one order.
>No
>A consolidation may be two orders combined going to the one destination

Yes, that makes sense now.

>>Yet, you have a design that allows the front load to ahve a different collection than the rear load.
>Oh no, I've just added the orderID to the consolidation table and found a big problem, oh no. there are multiple
>order numbers for the one consolidation line and I'm not sure how to connect these tables. Oh dear. I did
>delete the join as I thought that maybe the consolidation became the new order to consider, may be the tables
>don't need joined, not quite sure????

Don't panic, Rhonda! Sit back, take a deep breath and relax.

There's no point in just removing columns, adding columns, changing
joins etc. at this point. The basic steps are:
1. Information gathering. No computer needed (and if you do use one,
just stick to using only a word processor).
2. Designing the relational schema - definitely not yet on the server.
Some sketch paper and a set of pencils are the idedal tools for this.
3. Verify.
4. Code. This is the first time you should tell SQL Server what
columns to put in which table.

We're only in stage 1 at the moment.

>>Is it really
>>possible that one trailer picks up some loads from one collectionID
>>and some other loads at another collectionID, then delivers this all
>>at one deliveryID? If so, then you have some **MAJOR** redesigning to
>>do in all yoour other tables!!!
>
>I will need to revise my db schema, any thoughts that you may have, should you have grasped the scope of the
>project a bit better would be just wonderful, I'm struggling a bit.

As you see, I've taken the challenge.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)