How to Identify an Array

[rm]

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.

[David Portas]

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
--