CODEWITHSUNDEEP
databasedatabase-design
batman
batman

Reputation: 5370

How to deal with a field's data structure changing with time?

I'm designing a courier database.

In one parcel table, I need to have a field that will tell me something about the present state and location of a parcel.

If the parcel has reached the destination branch of the courier company, this field must point at information about the delivery person, etc.

If the parcel is still traveling across branches of the courier company, this field must point at information about the vehicle carrying the parcel, etc.

How do I deal with this heterogeneity?

Upvotes: 0

Views: 65

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95522

The column "other_columns" is a placeholder for all the other columns that might apply to the tables. Parcels have only two dispositions: in (t)ransit, or (d)elivered.

create table parcel_dispositions (
  parcel_id integer not null,
  disposition_timestamp timestamp (0) not null 
      default current_timestamp,
  parcel_disposition char(1) not null 
      default 't'
      check (parcel_disposition in ('t', 'd')),
  other_columns char(1) default 'x',
  primary key (parcel_id, disposition_timestamp, parcel_disposition)
);

create table parcels_in_transit (
  parcel_id integer not null,
  disposition_timestamp timestamp not null,
  parcel_disposition char(1) not null 
      default 't'
      check (parcel_disposition = 't'),
  other_columns char(1) not null default 'x',
  primary key (parcel_id, disposition_timestamp, parcel_disposition),
  foreign key (parcel_id, disposition_timestamp, parcel_disposition) 
      references parcel_dispositions (parcel_id, disposition_timestamp, parcel_disposition)
);

create table parcels_delivered (
  parcel_id integer not null,
  disposition_timestamp timestamp not null,
  parcel_disposition char(1) not null 
      default 'd'
      check (parcel_disposition = 'd'),
  other_columns char(1) not null default 'x',
  primary key (parcel_id, disposition_timestamp, parcel_disposition),
  foreign key (parcel_id, disposition_timestamp, parcel_disposition) 
      references parcel_dispositions (parcel_id, disposition_timestamp, parcel_disposition)
);

insert into parcel_dispositions values
(1, '2013-01-01 09:35', 't', 'x'),
(1, '2013-01-03 17:33', 't', 'y'),
(1, '2013-01-08 08:00', 'd', 'z');


insert into parcels_in_transit values
(1, '2013-01-01 09:35', 't', 'a'),
(1, '2013-01-03 17:33', 't', 'b');

insert into parcels_delivered values
(1, '2013-01-08 08:00', 'd', 'c');

In production, you'd usually deny access to the base tables, and build an updatable view based on something like this. (One view for parcels in transit, another for delivered parcels.) Application code uses the views, not the underlying tables.

create view all_parcels_in_transit as
select t1.*
from parcel_dispositions t1
inner join parcels_in_transit t2
        on t1.parcel_id = t2.parcel_id
       and t1.disposition_timestamp = t2.disposition_timestamp
       and t1.parcel_disposition = t2.parcel_disposition;

There are certain sensible constraints that SQL dbms don't yet enforce. For example, after delivery, the database shouldn't accept any more dispositions for a given parcel, but SQL dbms can't enforce that yet. (Can't enforce it declaratively. You could enforce it with triggers, though.)

Upvotes: 0

BlackICE
BlackICE

Reputation: 8916

You could do this by referencing two different tables from your parcel table, one representing branch of courier, other being information on destination. If one doesn't apply to the parcel then it would be null. If you don't want to break normal form you can have this info in a state table that you then join to these other tables, and if no info coolness back from either you know that status doesn't apply to the parcel.

Upvotes: 0

Raydot
Raydot

Reputation: 1576

Is there an infinite number of states or can you break it down to a discrete list?

If the answer is the latter -- which I assume is true given the "branches of the courier company" part of your question, you can create a look-up table cross-referencing trucks with id numbers. A simple join (http://www.w3schools.com/sql/sql_join.asp) should return the information you're looking for.

Upvotes: 1

Related Questions