create new sql tables correctly

Hi,

I need help to create tables the right way. this is my first time doing it.

My company asked me to create effective tables..

Here are things needs to be stored.

The goal is to keep track of the data.

Daily

--

- In the future they are expecting 50,000 + users...

- A user would be able to add data daily and every hour ( text can be from 25 characters to 100)

- And I would need to keep the data for 60-120 days after the data can be deleted. (I would have to write a script, that would run daily to check if the data is 120 days old than delete it, release space)

Got some questions:

- How much data a table can have?

- Can I create unlimited tables? or there is a max?
- I will be using freeSQL Server 2005 Express Edition, not sure if I need to buy the full version (SQL Server 2005 )

what I thought of doing is have 12 tables ( each has 1 week)...hmm.. doest sound good I know. I dont know how else to do.. (please advice)

|userId| DAY1 | DAY2 | DAY3 |DAY4 |DAY5 |DAY6 |DAY7

01 ...text will be added each hour... ..day2 text... ...etc

-->the problem is I dont know how much a table can handle text..

Please advice any ideas..

Thank you for reading..

this post is helpful to know limits of SQL Server 2005

http://www.sqlservercentral.com/articles/Administration/maximumcapacityspecificationsinsqlserver2005/1574/

I would suggest you should create only 1 normalized table with following fields
(userid, timestamp, message)

SQL Server can handle huge amount of data, in your above table design, it is not mandatory that user will enter data every hour, in that case that hour field will remain empty.

Hope it clears your doubt and might be useful.
Let me know in case of any further queries.

|||

Sorry, I am new to creating tables..

1) what does "normalized table" means?

2) The url you posted, describesSQL Server 2005 , and I wanted to use SQL Server 2005 Express Edition (free), are the specs the same? or there is a limitation?

3) I have checked the url, and under : Row per table, Tables per database says "LIMITED BY AVALABLE STORAGE" what does this mean?

--

back to my questions, you are saying put all the data to one table.

userID | timestamp | message |

0001 | (not sure what you mean by the timestamp) | Day1#9:text here#10text here..etc.. Day2... (this can be very long...) not sure if this what you mean?.

Please provide me an example of the table structure. ( i am trying to build, if the user does add text every hour)... (just to be safe)

Thank you for your help.

|||

1) what does "normalized table" means?
Ans:normalization is a technique for designingrelational databasetables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. http://en.wikipedia.org/wiki/Database_normalization

2) The url you posted, describesSQL Server 2005 , and I wanted to use SQL Server 2005 Express Edition (free), are the specs the same? or there is a limitation?
Ans: There is no difference in terms of database limitations between SQL Server 2005 and Express Edition. The only difference is some of the advance features are not available with Express Edition.

3) I have checked the url, and under : Row per table, Tables per database says "LIMITED BY AVALABLE STORAGE" what does this mean?
Ans: Limited by Available storage means, Free disk space of your server/computer. means there is no limit of storing data in tables.

--

back to my questions, you are saying put all the data to one table.

userID | timestamp | message |

here timestamp is dateandtime user has posted the message
e.g. data can be like this
1 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 13:00 some message

I hope this answers your questions.

|||

aadreja:

2) The url you posted, describesSQL Server 2005 , and I wanted to use SQL Server 2005 Express Edition (free), are the specs the same? or there is a limitation?
Ans: There is no difference in terms of database limitations between SQL Server 2005 and Express Edition. The only difference is some of the advance features are not available with Express Edition.

See the following link

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Two important limitations of Express are:
Maximum database size of 4 GB
SQL Backup is NOT supported.

|||

Thanks for your help,

Sorry I just got 2 more questions,

1) I just noticed at the specs that it says "Database size is Maximum 1~ terabytes" but number of rows/tables are unlimited, so its not realy unlimited, it has a limittation? or am I missing something?

2) the table example structure you showed:

aadreja:

1 12/12/2007 10:00 some message
2 12/12/2007 11:00 some message
3 12/12/2007 13:00 some message

that only shows that each user entered one hour a day. but if for example user#1 entered text for 9,10,11,12..etc would I store all in one table?

example:

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..

so it will be a very long table.. do you think it will be okay? or should it been designed differently? if each user will have 15 rows and It might have 50,000 + user.. Will it support that ?

Thank you for your help

|||

There is no limit to the number of rows in a tabe or or tables in your database. Only a limit to the size of the database.

We run a database which is logging about 50 measurements every 30 seconds and has been doing this for over a year now - at the moment this table holds 208,495,020 rows and there's no speed problem.

We are using SQL Server 2005 Workgroup edition, not express, and on a fairly powerful dual core dual processor server.

I think you should trial the system on Express - you can always upgrade to a more "full-bodied" version later if you need to.