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.
| Author |
Topic |
|
Imukai
Starting Member
29 Posts |
 Posted - 2008-03-26 : 11:05:16
|
| I'm hoping you guys can help minimize my headache and help me not to end up on TheDailyWTF... :)I need to set up a database on SQL 2000 to store data for a 256x256 grid (65,536 squares). I need to store data about what's in those squares, if anything. I also need to store, on a per-userbasis, which squares have been accessed.Storing what is in each square isn't that big of a deal since it's a one-time thing and I can handle a table with X, Y, [Content Stuff] that has 65,536 rows.The potential headache comes from the second part - storing which squares have been accessed on a per-user basis. The userbase being several thousand and growing.I need to be able to both check individual squares to see if the user has previously accessed it (date/time irrelevant). I also need to be able to pull a dataset containing all squares they have accessed, as well as those they have not yet accessed.Several "Bad Things" that have run through my head:1. Set up a 1:1 table for 1 row per user, with 65,536 columns. Assuming SQL would even let you go past 1,024 columns, this would be a nightmare plain and simple. It's not an option.2. Set up a 1:1 table for 1 row per user, with a TEXT field containing 65,536 1's and 0's. This reduces the rowcount but I would have to pass off the processing to the application level since I don't think I'd want to parse out a TEXT field in a stored proc. I don't think this is a viable option.3. Set up a 1:n table with three columns (UserID, X, Y) that would have "up to" 65,536 rows of data PER user. This seems the most logical and easiest to query, but the concept of 65k rows (potentially) per user over a many-thousand-user population seems like it's going to mount into one massive db.4. Break up the "grid" into smaller grids (say, 64x64) and do something similar to #2 but have 4096 1's and 0's in the TEXT field and up to 16 rows per user. This still seems inefficient to me and the parsing would still have to happen on the application end.Is anyone out there creative enough to suggest something I haven't thought of? I'd rather not sink the DB server if I can get away with it. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2008-03-26 : 12:01:53
|
| Logically #3 is correct. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-26 : 13:53:46
|
| I would go for #3.Could refernce the cells using an id 256 * x + y.It's not much smaller as id will be a smallint rather than x,y as tinyints.The user table will have very narrow columns so even though there will be a lot of rows it still won't take up much space.It would be indexed on user ID rpesumably so you would only be accessing a max of 65,536 rows per user.You may haver problems with blocking if it's high volume though so might ant to split it into multiple tables but that would depend on the bussiness needs.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Imukai
Starting Member
29 Posts |
Posted - 2008-03-27 : 08:56:19
|
| I figured that would be the route to go- I was just hoping there was a more creative way of doing it without breaking things. I even pondered storing a 256x256 b&w graphic for each user, and letting the app analyze the graphic for black pixels... while that might be creative, it's probably a lot more overhead than I care for. I'll do the 1:1. If anyone has a flash of inspiration tho, do let me know.Appreciate the feedback. :) |
|
|
|
|
|
|
|
|
New DB design headache