Re: Is there a bitfield type field in oracle?

From: Walt <>
Date: Wed, 30 Jan 2008 14:17:36 -0500
Message-ID: <>

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:

         NAME Varchar2(64) )

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

                  D DATE constraint trunc(d)=d (or use a trigger),
                  IS_RUNNING varchar2(1) )
 > (1,01/01/2008,'Y')

 > (1,01/02/2008,'Y')
 > (2,01/01/2008,'N')

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

Original text of this message