structure table .. - 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 2005 Forums

SQL Server Administration (2005)

structure table ..

Author

Topic

tarz
Starting Member

31 Posts

Posted - 2007-12-13 : 14:35:16

I'm new in creating tables. and I was asked to create table(s) to capture information using SQL server 2005 (express edition)

please read below and share your thoughts and opinions o what's the efficient and best way to structure the tables.

I need to capture user's input daily and every hour, text can be from 20-100 characters, and my company is expecting to have 50,000+users in the future...Also the hrs's text can be stored for max 120 days, after that data would be deleted from the table(s)
An example is put all together, (not sure if it is the right and efficient way)

An example is put all together, (not sure if it is the rigth and effectient way) and if database would support that.. and if it will be fast enougth to search for data

(table structure) - (example with 3 users)
userId | timestap | message
1 12/12/2007 10:00 some message
1 12/12/2007 11:00 some message
1 12/12/2007 12:00 some message
1 12/12/2007 13:00 some message
1 12/12/2007 14:00 some message
2 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 12:00 some message
3 12/12/2007 13:00 some message
3 12/12/2007 14:00 some message ...etc..

Thank you..

gavinjb
Starting Member

34 Posts

Posted - 2007-12-14 : 04:52:34

Hi,

I cant see any issues with this, if you are going to search I would make sure you index the columns you are likly to search, one thing I would add would be a ID or GUID as a PK (I always have a PK on a table)

I haven't used SQL Express much, but if I was using SQL Server 2005 I would a have a job that ran once a week to delete the rows of data I no longer needed. I would also have a job to do any maintenance on the database to keep the performance as good as possible as with 50,000+ users with pottentially several records a day is going to add up to a lot of data.

tarz
Starting Member

31 Posts

Posted - 2007-12-14 : 09:19:58

Thanks,
Just to make sure 1 table will be able to support some many records?
Could you please explain me when would the ID/GUID be useful to use? and
what kind of "job maintence on the database would I need to do to keep the performance good"?

Thank you.

pootle_flump

1064 Posts

Posted - 2007-12-17 : 06:14:08

Note that a GUID\ ID is not a requisite for a PK. If a user cannot enter more than one message for any time stamp then a combination of userId and timestamp is your natural, candidate key. Whether or not you use an ID (presumably Gavin is proposing an identity)\ GUID is a matter of design decision\ religious conviction. Try googling "surrogate Vs natural keys" for some pointers. It is a long and contentious topic.

How your table is used will determine the most efficient structure. I take it the text is simply free text right?

pootle_flump

1064 Posts

Posted - 2007-12-17 : 10:42:14

FYI:
http://sql-server-performance.com/Community/forums/t/24750.aspx

tarz
Starting Member

31 Posts

Posted - 2007-12-19 : 12:49:24

Thank you.

what type of "job maintences on the database would I need to do to keep the performance good"? except cleaning the data that was stored more than 120days..?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 12:54:43

In this case, I would prefer not to have a PK.
A clustered index over {TimeStamp, UserID} will speed up searches.

DELETE
FROM Table1
WHERE TimeStamp < DATEADD(HOUR, -2880, CURRENT_TIMESTAMP)

will delete all records older than 120 days (2880 hours).

E 12°55'05.25"
N 56°04'39.16"

tarz
Starting Member

31 Posts

Posted - 2007-12-19 : 15:33:57

Thanks for the example.
Please explain me what is a clustered index? what do you mean by" A clustered index over {TimeStamp, UserID} will speed up searches."

Also why would you not recommend to have a PK?

Sorry I am new to all of this, want to understand it clearly..

Thanks a lot...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 16:32:01

It seems the table is only holding some logging information.
You are not going to edit single records, are you?

And the delete is bulk-delete, not delete of single records?

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2007-12-20 : 03:13:55

quote:
Originally posted by Peso

In this case, I would prefer not to have a PK.
A clustered index over {TimeStamp, UserID} will speed up searches.

DELETE
FROM Table1
WHERE TimeStamp < DATEADD(HOUR, -2880, CURRENT_TIMESTAMP)

will delete all records older than 120 days (2880 hours).

E 12°55'05.25"
N 56°04'39.16"

Why not a pk? You've got a perfect natural key there. Why would you not enforce it? The optimiser can use the information that it is unique to produce optimal plans.

It is worth pointing out to the OP though that the clustered index you've recommended (and I like it) will speed up searches based on date, but will not help for questions like "What message has user 7 entered into the database?".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-20 : 04:06:15

Well, it seems it depends on what purpose the table will have.
What is OP going to do with the information stored there?

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2007-12-20 : 04:44:15