100x100 array design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

100x100 array design

Author

Topic

1fred
Posting Yak Master

158 Posts

Posted - 2003-03-03 : 16:00:44

On my web site, I display an array of 10,000 squares. Each square has his own status: active, innactive. Also, each customer has his own array. What is the best way to store the information of the array in a database system? A table for each of the array col, a binary string of 10,000 char. I think there would be about 100,000 customers that would use the array on this site.

Also, it has to be design, so I can query the hole array quickly, to show the status of the array for each square.

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-03 : 16:13:39

100 x 100 or 1000 x 1000?

Brett
8-)

1fred
Posting Yak Master

158 Posts

Posted - 2003-03-03 : 17:03:59

An Array of 100x100, so 10,000 squares. A square does not represent a customer. Each customer has his own 100x100 array. I want to track the status of each square in each customer array. I plan to have arround 100,000 customers

Is it more clear?

Thanks
Fred

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-03 : 17:05:59

Probably the easiest way to do this:

CREATE TABLE Array(
row tinyint NOT NULL CHECK(row BETWEEN 1 and 100),
col tinyint NOT NULL CHECK(col BETWEEN 1 AND 100)
CONSTRAINT PK_Array PRIMARY KEY CLUSTERED (row, col)
)

You would insert a row into this table corresponding to the matrix address ONLY when the square is checked. Here's some helpful sprocs:

CREATE PROCEDURE CheckSquare @row tinyint, @col tinyint AS
SET NOCOUNT OFF
INSERT INTO Array(row, col) VALUES (@row, @col)
GO

CREATE PROCEDURE UnCheckSquare @row tinyint, @col tinyint AS
SET NOCOUNT OFF
DELETE Array WHERE row=@row AND col=@col)
GO

CREATE PROCEDURE IsChecked @row tinyint, @col tinyint AS
SET NOCOUNT OFF
IF EXISTS(SELECT * FROM Array WHERE row=@row AND col=@col)
RETURN 1
ELSE
RETURN 0
GO

That way you only store squares that are checked, instead of storing the entire array. If you need to, you can add a column to the Array table for the customer's name, and adjust the sproc's to include a parameter for customer name too.

BTW, why are you storing a 100x100 array? What does it represent/model?

1fred
Posting Yak Master

158 Posts

Posted - 2003-03-03 : 17:19:17

Thanks for the help, great idea. I'm doing that for a kind of games in which each square represent a part of a picture. When the customer will do some action, a part of the picture will be revealed. So anytime the customer come to the web site, I want to show him his picture with all the revealed part. It may not be a 100x100 array, it is still at the thinking stage.

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-03-05 : 17:43:37

You could also just make the item a binary(10000), and have the front-end app deal with rows/columns and just store the binary data in the DB.

Just an idea.

Cheers
-b

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-05 : 18:49:23

Binary maxes out at 8000 bytes, and you couldn't index it in any meaningful way, or perform bitwise operations on it. Same applies for an image column, only worse. Even if you used bitmasks, anything more than 32 bits will need special handling for bitwise ops.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources