Table Design - Incorporating Schedule Changes

  • Thread starter Thread starter Fiaz Idris
  • Start date Start date
F

Fiaz Idris

Hello,

The problem: I have a database that keeps track of
the orders received from passengers to board on a ferry
as below:

tblFerrySchedule tblOrder
---------------- --------
Ferry (PK) 1-| OrderNo (PK)
Movement | OrderDate
DepartureTime | OrderTime
Port |-8 Ferry
.... PassengerName
.... ...


Ferry is (FerryNo like "3S123", etc.)
Value for Movement is ("Arr" Or "Dep")
1 to Many is ( 1 -| 8 ) <-- the 8 means many

We start to keep creating orders for any ferry that requires it.
Normally the ferry schedule doesn't change everyday, but it changes
for example once every 3 months to a completely new schedule, with
new, updated time, cancellations of ferries.

When that happens and I do update the changes on tblFerrySchedule
the tblOrder breaks. When the schedule time is changed for example:

Original tblFerrySchedule on 11Jan04
3S123, DEP, 12:30, CHN
Original tblOrder
12, 11Jan04, 11:30, 3S123

New tblFerrySchedule starting today 08Jul04
3S123, DEP, 10:30, CHN

and the tblOrder No.12 will point to a departure 10:30 while the
order is received at 11:30 which is impossible.

How can I refer to the correct schedule time in tblOrders on that
particular day the order was received regardless of the time changes
in the ferry schedule today?

Also, if I want to print today 08Jul04 what the ferry schedule was
on 11Jan04, I should be able to print the correct schedule at that
point in time instead of what was changed later.

How to setup such a table?

I hope I have made myself clear.
 
You have two options. The simplest is to copy the data into the Order
table, in the same way one copies the sale price of a product. The
alternative is to create a separate schedule table and store a reference to
it in the Order table. That will save you some storage space, but add
significantly to the complexity of your system.

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress
 
Back
Top