Professional Web Applications Themes

interesting table matrix problem - Microsoft SQL / MS SQL Server

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

  1. #1

    Default 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.
    Jay Guest

  2. #2

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

    Steve Guest

  3. #3

    Default 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


    David Guest

  4. #4

    Default 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. 
    Jay Guest

  5. #5

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

    Steve Guest

  6. Moderated Post

    Default Re: interesting table matrix problem

    Removed by Administrator
    Joe Guest
    Moderated Post

Similar Threads

  1. Interesting Flash8.ocx Problem
    By alperadatoz in forum Macromedia Flash Player
    Replies: 5
    Last Post: May 9th, 04:35 PM
  2. OKI Dot matrix printer configuration problem in Tru64 OS
    By Vikas in forum Linux / Unix Administration
    Replies: 0
    Last Post: July 2nd, 03:34 PM
  3. Interesting Problem
    By Smitty in forum ASP.NET Data Grid Control
    Replies: 5
    Last Post: November 16th, 09:34 PM
  4. A particularly interesting problem...
    By Will Hogben webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 4
    Last Post: October 7th, 06:57 PM
  5. Interesting Problem with IIS
    By Terry Murray in forum ASP Database
    Replies: 1
    Last Post: August 25th, 01:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139