interesting table matrix problem

i have an interesting problem (may be simple to

experienced programmers): i want to create an n x n table

that represents an n x n matrix, where there are n rows

and n columns. the 1st row has values that equal the

column names (this is like a crosstab query in access).

the trick is that rows, and therefore columns, must be

created dynamically so that the table is always n x n. i

do not know of any way to create or alter a table with

variable column names and have not found any

doentation on how to do this.

the only solution i have now is sloppy: create an n x 2

matrix (table) with the 1st column representing the row-

column identity and the 2nd column representing the row-

column value, which is tantamount to joining the matrix

row(or column) vectors head-tail. i could easily create

matrixes with matlab, but adding some call to matlab.exe

would be too much process overhead (although this would

be necessary for more complex vector ysis, say for

neural network ysis).

what is this for? i want to create an assignment

permissions matrix for our database where, if the value

of, where M is the table/matrix, M(i,j)=1, the ith

staffmember can assign assignments to the jth

staffmember. for example, the director can assign anyone

else assignments while the lowly support staff, such as

myself, can assign assignments only to themselves.

Re: interesting table matrix problem

Jay,

The solution you have is on the right track. This

sort of thing works well:

create table Matrices (

MatrixID int not null,

mrow int not null,

mcol int not null,

value <whatever>,

primary key(MatrixID,mrow ,mcol ),

unique (MatrixID,mrow ,mcol ,value) -- if you want column-major stuff

to be fast

)

It turns out to be very efficient, and it's effectively a "sp

matrix" data type, and can save space if there are lots of

missing values. It also works when row and column are not

integers - the only thing that's awkward is not having the usual

a[i,j] notation.

The down side of what you are trying to do is that while SQL

Server provides column-level security, which is what I think

you're planning to use, it doesn't provide row-level security.

Row-level security can be emulated with views containing

a filter like

where user_column = suser_sid()

it's not a perfect solution. There are some trade-offs

between security and performance. I think the view idea

is safe for protecting against updates and deletes, though.

-- Steve Kass

-- Drew University

-- Ref: DA18F7B6-A46C-44DB-B876-0104DC2A93EE

Jay wrote:

Re: interesting table matrix problem

"Jay" <com> wrote in message

news:043b01c3604f$45ccabf0$gbl...

You want to store a mathematical relation. Given a set, D, you want to

define a binary relation M on D. For each i,j in D, you want to store

whether M(i,j) holds.

So far so good. But your idea for storing the relation is wrong.

You see, this kind of relation is the "relation" in Relational Database

Management System.

An RDBMS exist solely to store such relations. But it does not store a

matrix representation of the data like this,

M|1 2 3 4

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

1|0 1 1 0

2|0 0 1 0

3|1 0 0 0

4|0 0 1 0

Rather it stores an enumeration of the elements of M.

Remember, M is a set, here M = {(1,2),(1,3),(2,3),(3,1),(4,3) }.

A RDBMS just stores the ordered tuples comprising the relation in a table.

In fact a relation is just a table with unique column names and no duplicate

rows.

M

i j

---

1 2

1 3

2 3

3 1

4 3

So if you want to know if the relation M holds between any 2 staff members,

you just need to check for existence of a row in the table M.

David

Re: interesting table matrix problem

thanks David and Steve for your replies.

if I could define the column names as row entries, this

would be much more efficient and much less administrative

overhead; I would have to update columns and their

respective relationships every time staffing changed.

Using 2 identical tables, each with a unique column

representing each unique staffmember, how do I define

what the assignment relationship is between the ith row

of 1 table and the ith row of the other; can

relationships between rows be defined without using a 3rd

table (the 3rd table would represent the set of entries

which define the relationship - something i referred to

as "sloppy" earlier)?

this is easily done with a crosstab q in access, but sql

doesn't seem to have a good method for creating pivot

tables. books online has the example

________________________________________

This is the SELECT statement used to create the rotated

results:

SELECT Year,

SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,

SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,

SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,

SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4

FROM Northwind.dbo.Pivot

GROUP BY Year

GO

________________________________________

but doesn't seem like an elegant solution to me, even if

i use control loops for the iterative process.

thanks for the lesson in relationships though - something

i'm sure we could all use.

[/ref]

table [/ref]

i [/ref]

row- [/ref]

row- [/ref]

matlab.exe [/ref]

anyone

>

>You want to store a mathematical relation. Given a set,[/ref]

D, you want to

want to store

is wrong.

Relational Database

does not store a

(4,3) }.

relation in a table.

names and no duplicate

any 2 staff members,

table M.

Re: interesting table matrix problem

Jay,

Maybe I misunderstood something, but I was suggesting

that you store the information you want to store in a matrix

in three columns instead: (row, column, ValueOf_M_row_col).

As David pointed out, if you have a 0-1 matrix, yoiu can use

two columns (row, column) and only include rows for which

ValueOf_M_row_col is 1.

If you are asking for a real materialized table where the column

a value appears in indicates the staffmember it refers to, you are

asking how to make a model work that uses column positions or

names to hold facts. In my book, that's always a mistake. Column

names identify attributes, and shouldn't be used for other things.

The fact that person A can tell person B what to do should be

represented by a row containing the pair (A,B). It sounds like

you want it to be represented by a value in the columnB column

of the person A row. I can't think of anything but perhaps a

report that is made easier with such a model. Just listing or

counting all the employees person A can assign tasks to is a

mess.

Steve

Jay wrote:

[/ref]

>table

>

> [/ref]

>i

>

> [/ref]

>row-

>

> [/ref]

>row-

>

> [/ref]

>matlab.exe

>

> [/ref]

>anyone

>

>

>>You want to store a mathematical relation. Given a set,

>>

>>[/ref]

>D, you want to

>

>

>want to store

>

>

>is wrong.

>

>

>Relational Database

>

>

>does not store a

>

>

>(4,3) }.

>

>

>relation in a table.

>

>

>names and no duplicate

>

>

>any 2 staff members,

>

>

>table M.

>

> [/ref]

Re: interesting table matrix problem

>> I want to create an n x n table that represents an n x n matrix ...

<<

CREATE TABLE Matrix

(i INTEGER NOT NULL

CHECK (i BETWEEN 1 AND <<n>>),

j INTEGER NOT NULL

CHECK (j BETWEEN 1 AND <<n>>),

val FLOAT NOT NULL,

PRIMARY KEY (i,j));

I have a short section on how to write standard matrix math in SQL in

SQL FOR SMARTIES you can look up.

[/ref]

NO! A table is a model of a set of entities; you have to know the

attributes before you can build a model.

[/ref]

where, if the value of, where M is the table/matrix, M(i,j)=1, the ith

staffmember can assign assignments to the jth staffmember. <<

Permissions are usually done with a hierarchy and not an array model --

circular references, lack of clear authority in a partial ordering, etc.

are a .

--CELKO--

===========================

Please post DDL, so that people do not have to guess what the keys,

constraints, Declarative Referential Integrity, datatypes, etc. in your

schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Don't just participate in USENET...get rewarded for it!