Storing Ranges of Values: Help Requested

Storing Ranges of Values: Help Requested

Post by Chris Secor » Fri, 16 Aug 2002 04:40:33



I am working with a database that contains classroom standards.  Each
standard is associated with a grade-level (1st grade, 2nd grade etc.).  Some
standards are associated with multiple grade levels (k-5, or 8-12 for
example).

Currently, the grade is stored in a char field.  The table looks like this:

create table standard (description char(10), grade char(4))

description, grade

standard 1, k

standard 2, 4

standard 3, k-5

standard 4, 2

Obviously I'll need to fix this.  I'd appreciate any suggestions.

The chief requirement is that I be able to specify a grade and return all
the applicable standards.  For example, if 2nd grade is specified, standards
3 and 4 should be returned.  Here is what I'm considering.

create table grade (gradeid int, shorttext char(10))

create table standard (standardid int, description char(10))

create table link_grade_to_standard (linked int, gradeid int, standardid
int)

This way, standards that apply to multiple grades will have multiple entries
in the link_grade_to_standard table.

chris

 
 
 

Storing Ranges of Values: Help Requested

Post by Steve Kas » Fri, 16 Aug 2002 12:30:29


Chris,

  There's no right answer, and the solution you've suggested could
work.  If you want to avoid so many tables, and if you want to avoid
the possibility of non-contiguous grades for a standard (assuming that
is impossible), another possibility is (first try)

create table standard (
  description char(10),
  grade_from char(4),
  grade_to char(4)
)

To make the data entry easier, you could have a trigger to fill in
grade_to to be equal to grade_from if it's not entered - the problem
here, though, is that it won't quite work unless you use int instead
of char(4) and keep things in order so you can use BETWEEN, or do
something artificial like naming the grades 0_K, 1_1, 2_2, ....  You
could improve that this way:

create table standard (
  standID int primary key,
  description char(10),
  grade_from int,
  grade_to int
)

create table grade ( -- to help perhaps with data entry or reports of all standards
  gradeIndex int primary key, -- this represents the actual ordering, so, e.g., k has index 0
  gradeName char(4)
)

and then have grade_from and grade_to reference grade.gradeIndex.  Your
query will look something like

create procedure ShowStandards (

) as


    select gradeIndex from grade

  )


  select standID, description
  from standard S

go

In any case, these are just some more ideas - unless you have huge quantities
of data, various things will work, and you should just think about issues
like data integrity (avoid the possibility of allowing nonsense grades or ranges
to creep in) or the possibility of growth (new grades, new information about
grades or standards...

Yet another solution would be to have named ranges with defined start and
stop grades, so K-4 could still be a valid entry.  What might tilt the balance
towards or away from making a grade range an entity deserving of a
table is whether there are standard ranges with properties you might
keep track of - if you might have K-2, K-3, 2-4, or just about anything, then I
probably wouldn't pick a solution that puts every possible range in a table.

Sorry if I've rambled a bit, but I hope it helps.

Steve Kass
Drew University


> I am working with a database that contains classroom standards.  Each
> standard is associated with a grade-level (1st grade, 2nd grade etc.).  Some
> standards are associated with multiple grade levels (k-5, or 8-12 for
> example).

> Currently, the grade is stored in a char field.  The table looks like this:

> create table standard (description char(10), grade char(4))

> description, grade

> standard 1, k

> standard 2, 4

> standard 3, k-5

> standard 4, 2

> Obviously I'll need to fix this.  I'd appreciate any suggestions.

> The chief requirement is that I be able to specify a grade and return all
> the applicable standards.  For example, if 2nd grade is specified, standards
> 3 and 4 should be returned.  Here is what I'm considering.

> create table grade (gradeid int, shorttext char(10))

> create table standard (standardid int, description char(10))

> create table link_grade_to_standard (linked int, gradeid int, standardid
> int)

> This way, standards that apply to multiple grades will have multiple entries
> in the link_grade_to_standard table.

> chris


 
 
 

Storing Ranges of Values: Help Requested

Post by Chris Secor » Sat, 17 Aug 2002 00:14:26



Quote:> the possibility of non-contiguous grades for a standard

I think (hope) that if I model the relationship correctly, it would be
possible to have non-contiguous grades - even though that will probably
never happen.

Quote:> To make the data entry easier, you could have a trigger to fill in

thanks for the tip.  I hadn't considered triggers.

Quote:> like data integrity (avoid the possibility of allowing nonsense grades or
ranges
> to creep in) or the possibility of growth (new grades, new information
about
> grades or standards...

> Yet another solution would be to have named ranges with defined start and
> stop grades, so K-4 could still be a valid entry.  What might tilt the

balance

right.  Grades are constant. There should never be the need to allow someone
to just type in a grade when entering a standard.  That's why they should be
in a separate table.  I definitely can not have specific ranges (like K-4)
defined anywhere.  That would defeat the whole purpose of modeling the
relationship through the database's design.

Thanks for the help

chris

 
 
 

1. request help "Number out of Range"

  This really looks like the table does NOT have a long integer field type.
  Long integers are specified with "I", logicals with "L", and short ints
  with "S"

  In OPAL, when I try to put 38200 into a smallint, I get a long
  message telling me the data is nuerically too large ... not the
  one you mention.

--
--------------------------------------------------------------------

  Coupled Climate Dynamics Group/971
  NASA Goddard Space Flight Center
  Greenbelt, MD

  Envision Whirled Peas

2. Q:Tabed List Bound

3. requesting Value of ID from Stored Procedure to JSP

4. Help - SQL Server 7 SP2 -where's the setupsql.exe?

5. help - Value out of range error

6. Patch for PostgreSQL 7.0.3 to compile on Tru64 UNIX v5.0A with Compaq

7. HELP - Numeric value out of range errors

8. UK/NE - Progress developers needed

9. Requesting help setting field in each record of 1 table to value in other table

10. Urgent Salary range request

11. Request for "date ranges find"

12. Request Help on stored procedure.....

13. Random - Stored Procedure Request Help