Database design for container tracking system

[bigoby]

Hi,

I am designing a shipping container tracking system for my work and would like to know what the best design is for this.

There are many excel sheets that provide data on the status of containers along the supply chain i.e. shipping schedule, data from the logistics supplier showing status at the wharf, data for movements on our site etc.

Each container has a unique identifier according to what stock it carries and also a container no. designated by the shipping line. I was planning on use the first one as a primary key.

My first instinct was to have a main table that contained all of the information, with queries that updated the fields from the other data sheets.



Could you please advice if this is an appropriate design?

[amrut]

See this to start with http://www.databaseanswers.org/data_...ping/index.htm

[bigoby]

Thanks for getting back to me. That does seem to be useful, however my problem is less complicated than that.

There are no customers, products or orders. There is only containers and dates on when they were at various points in the supply chain.

We want to be able to track how long each container was at a certain point e.g. at the wharf, on our site, in transit etc.

[amrut]

To start with, create three tables -
tblContainers : ContainerID as PK, ContainerNAme as identifier for a container
tblLocations : LocationID as PK, LocationNAme
tblContainerMovement which will have Container(foreign key tblContainers) ,Location(foreign key tblLocations) and EntryDate ( date the container entered a location) and ExitDate ( date the container exited a location).

The field exitdate may not be required as a query may be able to pull the duration a container was at particular location( by subtracting this entryDate from the next immediate EntryDate for that particular container). Post your questions and experts in the forum will guide you.

[bigoby]

That sounds like the right way to go. I will try this and post again if any problems. Cheers!

[bigoby]

I have made the tables you suggested and it seems that this solution will work.

However, I need to determine the status of each container according where it is. I can't seem to figure out a way of doing this from the tblContainerMovements because there will be duplicate entries. E.g. the same container will be listed with its date at the Wharf, on site etc. How can I determine the status from duplicate entries?

[amrut]

E.g. the same container will be listed with its date at the Wharf, on site etc.

You need to create a business (rule if it suits ) that a container can be at only one location on a particular date at a particular time say 8:00AM and the container's entry date to a new place will be the next day. If this doesn't fit in your business rule, then you have to save the time along with the date. I have never dealt with data involving time.
One more option is to save the date & time in numerical form, though displaying in date form. Try this ?CDbl(Now) in immediate window to see date & time in numerical form
Maybe someone else on forum has a better idea .

[bigoby]

I'm not sure if I explained my problem properly, sorry. So say I have these movements:

Container	Location	EntryDate
A0001	Warf	20/04/2014
A0001	Yard	22/04/2014
A0001	Shop	26/04/2014
A0002	Warf	25/04/2014
A0002	Yard	26/04/2014

I want a query to produce this summary:

Container	Location	Status
A0001	Shop	Unpacked
A0002	Yard	Sealed

[amrut]

Do you need their last location as per records? Use a "group by" in your query with max of entry date.

[ArviLaanemets]

Create a saved query, p.e.

qLastDate = SELECT Container, MAX(EntryDate) AS LastDate From tblMovements

Now you can have the query

SELECT mov.Container, mov.Location, mov.EntryDate From qLastDate ld INNER JOIN tblMovements mov ON mov.Container = ld.Container AND mov.EntryDate = ld.LastEntry