List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 7 1999 5:37pm
Subject:Re: Sparse tables in MySQL
View as plain text  
terry jones wrote:
> 
> >>>>> "Marco" == Marco Becchio <becks@stripped> writes:
> Marco> What about a table like this?
> Marco> ----------------------
> Marco> |        matrix   |
> Marco> ----------------------
> Marco> | matrixrow       | INT
> Marco> | matrixcolumn    | INT
> Marco> ----------------------
> Marco> One table row for each non-zero matrix cell.
> Marco> to get the cell value you could do: select count(*) from matrix where
> Marco> matrixrow=.... and matrixcol=....
> 
> Hi Marco, thanks for your fast answer. I understand this, and it does
> solve the problem I stated.
> 
> Unfortunately, I now see that I described something a little simpler
> than what I actualy need to do. Here's my actual situation.
> 
> I have a column in a table that holds ID numbers. The rest of the
> columns indicate properties that can either be 1 or 0. If you think of
> the ID numbers as representing objects, I want to be able to ask
> questions such "select all the objects that have property 12 and do
> not have property 17".
> 
> With a table containing the 1's and 0's, I can do this easily.
> 
> With a representation like yours, I don't see how to do it because I
> want to select on things that are not in the table (i.e., not having a
> property). In your representation I could use my object IDs as your
> matrixrow and my property (column) number as your matrixcolumn. That
> would allow me to select all the IDs that did have some set of
> properties, but not ask a question with a negative term like the
> select above.
> 
> Perhaps there is something in SQL that will let me do this. I know
> almost nothing about SQL (as well as knowing nothing about databases).
> 
> Sorry for the confusion, and thanks for your original help. I think I
> have stated things clearly now.
> 
> Regards,
> Terry.

Hi Terry

What you search for is the SET column type of mysql.
In it you can store up to 64 different boolean types by name.
Just read manual section '7.2.7.4 The SET type' and '7.3.9 String functions' over
functions FIND_IN_SET(...) and MAKE_SET(...).

Note:
This is an SQL enhancement in mysql only!
In other DBMS you have to do this by hand.
Then you have to use boolen operations to create an integer which you store in the DB.

Tschau
Christian

Thread
Sparse tables in MySQLterry jones7 May
  • Re: Sparse tables in MySQLMarco Becchio7 May
  • Re: Sparse tables in MySQLterry jones7 May
  • Re: Sparse tables in MySQLChristian Mack7 May
  • Re: Sparse tables in MySQLterry jones8 May
  • Re: Sparse tables in MySQLSasha Pachev8 May
  • Re: Sparse tables in MySQLterry jones10 May