Re: Database design problem: 3D array

In article <39EE6662.42FCEC96_at_sympatico.ca>, Jeff Dever <jsdever_at_sympatico.ca> wrote:
>Question up front:
>Is it acceptable to have table names that are identified by index keys
>in another table? I am expecting to have a family of tables that
>corespond to rows in indexing table and need a way to look them up.
>Essentially, I want to build a 3 dimentional array in the database and
>don't know the normal way to do this.
>
>Description of my particular problem:
>Essentially I have projects, systems and requirements. Each project has
>certain requirements for systems. The requirements are specified on a
>per project basis. A requirement is composed of a project, a system,
>and an array of integers that represent the number of shifts that will
>be required in each week of the projects duration.
>
>My problem is that the projects will have different durations and
>therefore different number of elements in the array of shifts. In terms
>of the database, I will need a different table for each projects
>requirements with the number of columns to match the number of weeks the
>project is in effect.
>
>For example, if I had a project number 567 which had a duration of 4
>weeks (from week 42 to week 45 in year 2000) which required system 33
>and 44, the table might look like this:
>
>REQUIREMENTS567
>system_code wk200042 wk200043 wk200044 wk200045
>33 4 3 2 1
>44 1 4 4 1

Jeff,
Your present table describes (numbers of) shifts that depend on: project, system_code and week. Following the concept of normalization, this means that those three should form the primary key of your table

Table REQUIREMENTS

Columns    project    system     week       shifts
           567        33         200042     4
           567        33         200043     3
           567        33         200044     2
           567        33         200045     1
           567        44         200042     1
           567        44         200043     4
           567        44         200044     4
           567        44         200045     1

You may call each column in the primary key a "dimension" if you wish. Received on Thu Oct 19 2000 - 09:18:23 CEST