Posted by David Portas on 11/20/06 22:49

rm wrote:
> I have seen several examples explaining the fact that a table
> containing a field for each day of the week is for the most part an
> array. An specific example is where data representing worked hours is
> stored in a table.
>
> CREATE TABLE [hoursWorked] (
> [id] [int] NOT NULL ,
> [location_id] [tinyint] NOT NULL,
> [sunday] [int] NULL ,
> [monday] [int] NULL ,
> [tuesday] [int] NULL ,
> [wednesday] [int] NULL ,
> [thursday] [int] NULL ,
> [friday] [int] NULL ,
> [saturday] [int] NULL
> )
>
> I had to work with a table with a similar structure about 7 years ago
> and I remember that writing code against the table was pretty close to
> Hell on earth.
>
> I am now looking at a table that is similar in nature - but different.
>
> CREATE TABLE [blah] (
> [concat_1_id] [int] NOT NULL ,
> [concat_2_id] [int] NOT NULL ,
> [code_1] [varchar] (30) NOT NULL ,
> [code_2] [varchar] (20) NULL ,
> [code_3] [varchar] (20) NULL ,
> [some_flg] [char] (1) NOT NULL
> ) ON [PRIMARY]
>
> The value for code_2 and code_3 will be dependently null and they will
> represent similar data in both records (i.e. the value "abc" can exist
> in both fields) . For example if code_2 contains data then code_3 will
> probably not contain data.
>
> I do not think that this is an array. But with so many rows where
> code_2 and code_3 will be NULL something just does not feel right.
>
> I will appreciate your input.


A table is not an array. It is a relation. Unlike arrays, relations are
not addressable by an index structure but only by the values they
contain. A relation with N attributes is N-dimensional but that doesn't
make it an N-dimensional array.

I have only your column names to go on. Your HoursWorked structure is
surely very impractical, not least because of the difficulty of
aggregating data across multiple days. The second case is trickier to
interpret. At the very least it seems probable that it isn't normalized
appropriately because of what you have said about the dependencies.
Think Fifth Normal Form and satisfy yourself about the appropriateness
of the design. Design by newsgroup is really not much more than
guesswork.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]