I am working with a database that tracks educational courses. The
provider of the course is responsible for entering in the data about
the course. One of the attributes of the course is how long it is. It
may be expressed in any of various units: minutes, hours, days, weeks,
months, or years. Originally I modeled it as two columns - one a
decimal for the duration and another an FK into a duration unit table
to show the unit that was used. I also added a constraint such that
either both columns had to be NULL or both had to have a value - a
user could not fill in one without entering in the other.

Now I'm wondering if a better way would have been to include it all in
one column, similar to this (this is in T-SQL):

CREATE TABLE dbo.Courses
(
course_id INT NOT NULL,
duration VARCHAR(20) NULL,
CONSTRAINT CK_Test_Durations_duration CHECK (
ISNUMERIC(SUBSTRING(duration, 1, CHARINDEX(' ',
duration))) = 1 AND
SUBSTRING(duration, CHARINDEX(' ', duration) + 1, 20) IN
('minutes', 'hours', 'days', 'weeks', 'months', 'years')),
CONSTRAINT PK_Test_Durations PRIMARY KEY CLUSTERED (id)
)

I'd be interested in everyone's input on the two approaches as well as
any alternative ways to model this.

Another quick note... I cannot just use a single duration unit (for
example, express everything in seconds or minutes). There may be
reporting on this information, but I don't anticipate any math being
performed on it. We don't do any scheduling - it's just for display
and informational purposes so that when someone is looking up a course
to decide what to take they can get an idea of the time commitment. I
want to avoid completely free form text though since the current
system which we're replacing allows for that and it has created a bit
of a mess.

Thanks!
-Tom.

On 2009-05-01, Aardvark <trhummel (AT) gmail (DOT) com> wrote:
postgres has a interval datatype which internally includes atleast 4
different types

seconds
days (not all days are 86400 seconds)
months
and years.

and allows useful multples like miuntes, hours, and weeks,
and can be used in timestamp arithmetic.

ok, you may not have a type like that.

how do you want to use in in queries?

Aardvark wrote on 01.05.2009 16:36:
the "original" unit so that for display purposes you can still display
whetever the user entered and still be flexible enough to do anything with that
field you might want in the future.

The interval datatype as suggested by Jasen is also a good idea.

Thomas

On May 2, 4:36*am, Jasen Betts <ja... (AT) xnet (DOT) co.nz> wrote:
Standard SQL has had a full interval datatype since SQL92.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
================================================== ============
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)