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 |
|
Tomski
Starting Member
2 Posts |
 Posted - 2005-02-05 : 15:42:59
|
| I really had no idea how to name the subject. Here goes.I have about 1,000 records in my mySQL DB and this number grows. I need information for these records for each record in the db. Now that would mean having 1,000,000 records in the DB and growing and growing exponential.Each record is a user and I need to know per record if a user has edited it within the last 24hs. I also need to store the IP address of that specific user. So in short each user edits another users record and I need to store in the DB per user who did what to who.Now there must be an easier way than to create 1,000,000 records. I hope you guys can help me out here. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-05 : 15:57:39
|
| 1. We're a SQL Server site, not mySQL. You may want to try another forum.2. Nothing you've described suggests that you'd end up with 1 million rows, unless all 1,000 users edited the 999 users' rows. This makes no sense.3. A million rows would be zero problem for SQL Server, and probably no problem for mySQL, but that would depend on what exactly you're trying to do with those rows. |
 |
|
|
Tomski
Starting Member
2 Posts |
Posted - 2005-02-05 : 16:02:02
|
| SQL and mySQL work about the same way that's why I came here.I know how it sounds, but the program I have needs to update all the users for each user and then when a user checks to see what is still left for him to do he must not see the ones he did within the last 24hs. So the DB must only return the ones that he hasn't edited.So either I create a table with 1000 rows and 1000 columns or a table with 1million rows... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-02-05 : 16:16:25
|
quote:
but the program I have needs to update all the users for each user
Why?quote:
So either I create a table with 1000 rows and 1000 columns or a table with 1million rows
You still haven't explained anything that suggests either one of these is a requirement. It would be better to describe what the requirements of the application are, and why a particular feature needs to be included. Don't explain HOW you want to do it, just describe the WHAT.quote:
SQL and mySQL work about the same way
That's not even halfway true, sorry. Except for the most basic SQL syntax, mySQL and SQL Server are significantly different programs. It's possible to make the same table designs work on both systems, so it's not really an issue for you. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-05 : 16:46:21
|
| Each record is a UserI need to know per record if a user has edited it within the last 24hsI also need to store the IP address of that specific userThe first statement is incorrect - it might be that each record (row) in a table contains information about a user but it's not the user.Call that table UserYou just need a table to contain update info. This could just contain the User_ID of the updated table, User_ID of the updating user, date of update and ip address or it could also contain the data which has changed (and audit trail).Either way the User table won't be affected apart from bein updated.This other table you can purge of data older than 24 hrs in a batch job.When you want to know if a user has udated another user just query the table.==========================================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. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-05 : 17:32:19
|
| Only when a user actively does something to another user, store that action.You are describing a worse case scenario, when every user does something to every other user.Are you counting a "non action" between 2 users as an action as well ?Please describe more about the logic behind all this.rockmoose |
|
|
|
arpp
Yak Posting Veteran
61 Posts |
Posted - 2005-02-06 : 00:36:53
|
quote:
Originally posted by Tomski
SQL and mySQL work about the same way that's why I came here. needs to update all the users for each user and then when a user checks to see what is still left for him to do he must not see the ones he did within the last 24hs.
Out of curiosity, what are these guys doing to each other? and Are you looking for some kind of an Audit Trail? |
|
|
|
|
|
|
|
|
Storing information in tables