Re: Is there a bitfield type field in oracle?

tb wrote:

> Solution 3) looks like a relational database. Go one step more.
>
> REF_TBL_TRAIN (ID, NAME) reference table for all trains
> (1,'TRAIN A')
> (2,'TRAIN B')
> (3,'TRAIN C')
> REF_TBL_WEEK (ID,WEEK) reference table for all weeks in
> the year (54 entries for one year)
> (1,'Week 1') ... (52,'Week 52')
>
> REF_TBL_DAY_WEEK (ID,RUNNING_DAY) reference table to specify the
> days a train is running (create only combinations you need)
> (1,'YYYYYYY'), (2,'YYYYYYN') or something more simpler.

I've had to work with 'NYNNY' data designs like this, and all I can say is that the label is apt.

1. Composite fields (i.e. a column that holds more than one piece of information) are a red flag for bad data design.
2. Representing date information by "rolling your own" means a lot of extra work, and probably errors (from leap years etc.)

A relational database that takes advantage of the date datatype would look something like this:

TRAINS (ID NUMBER PK,
         NAME Varchar2(64) )

 > (1,'TRAIN A')
 > (2,'TRAIN B')
 > (3,'TRAIN C')

TRAIN_RUN_DATES (ID NUMBER FK to TRAINS table,
                  D DATE constraint trunc(d)=d (or use a trigger),
                  IS_RUNNING varchar2(1) )
 > (1,01/01/2008,'Y')

//Walt Received on Wed Jan 30 2008 - 13:17:36 CST