Visual Basic and SQL database design

75 pts.
Tags:
Database design
SQL Database
VB.NET
Visual Basic
i have vb.net & sql  application

sql database have one tabe with 37 column (there is no normalization)

after runing the application more than three million of rows will enterd by users .

I'm afraid  it Will give a bad performance  or have (out of memory massge)

so i Made a solution (i make [strong]Queries(with vb.net  wich will returnd only 800 row of data at atime[/strong])(max row size 1200)  

 is that the perfect solution .

plz  heeeeeeeeeeeeeeeelp

 



Software/Hardware used:
sqlserver

Answer Wiki

Thanks. We'll let you know when a new response is added.

As others have suggested, the table needs to be broken up and normalized. In a salary application, I venture to state that about 70% to 80% of data is static month to month. Consequently your monthly replication of 27000 of rows (with 37 columns) is a not good design. You really need to take a close look at the design and discuss with application programmers and DBAs.
Good luck.

Sorry, I did not mean to Answer it Under “Wiki”!!!

i do not know why it came that way – I must have posted my comments using not proper procedure!!

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    if this is new, (since you said the users will enter) a better solution would be to design and use a proper database (normalized and indexed)
    8,350 pointsBadges:
    report
  • carlosdl
    We don't even know what the application is going to do with those records. If you really want to get answers you will have to provide more details. P.s. But as BigKat suggested, the first step would be to re-design the database.
    70,200 pointsBadges:
    report
  • msmj86
    it is a salary system contain salares of up to 27000 employ salary table have 37 column every month i must Generate new 27000 rows with same data of Previous months rows in the salary table (i make function with vb.net to do this) normalization will make the generation of next month row so Difficult what I'm concerned about is the possibility that the program Will have a bad performance or give (out of memory massge) when the rows numbers will increased by time in the future AND does paging solve this problem and prevent it from appearing? best regards
    75 pointsBadges:
    report
  • Kccrosser
    Unless you have a really small database server, performance shouldn't really be a problem. 27000 rows per month is nothing - 300,000 rows/year or 3 million rows over 10 years. A payroll type of application really should be normalized. As others have said, there are probably a lot of the 37 columns that are repetitive from month to month (i.e., all the information about the "person" [name, address, phone, title, employer, employment status, etc.]) while the "dynamic" information (hours worked, pay amount, deductions, etc.) could definitely vary from month to month. However, normalizing the data in such a small database isn't going to affect performance that much, as long as you have some "key" information on those rows. You need at least a transaction date/time field, a payroll period identifier field (or at least a period start date/time and period end date/time field), and a person identifier field. With those, you can probably query the information fairly effectively. When designing a database, the proper first step is really to try determine what information is going to be required FROM the database and with what frequency. Then design your database to optimize for the most frequent (and most important) uses.
    3,830 pointsBadges:
    report
  • msmj86
    thanx kccrosser but !. in my case i have all the 37 column are Repeated monthly ( Except name column ) Because of that in normalization i must repeat rows with more that one table so i dont want to have normalization is there any proplem if i have table with 37 column
    75 pointsBadges:
    report
  • Kccrosser
    There is no problem with tables of 37 columns or more. Most database systems will allow up to 255 columns (although that would likely be a very poor database architecture). I am working with production databases with millions of rows, and there are 39 normalized tables (of 1423 tables) in that database with over 50 columns each. There are many good books (and online articles) on database normalization and the reasons to normalize a database. However, normalization isn't necessarily going to improve performance, and can often slightly degrade performance. Normalization is primarily intended to avoid data redundancy and generally make database maintenance and navigation easier.
    3,830 pointsBadges:
    report
  • msmj86
    thanx kccrosser
    75 pointsBadges:
    report
  • philpl1jb
    Pay Stub, 37 columns makes total sense to me. - there are all those things on your pay stub that are unique to the SSN-Year-Month even when they typically have the same values (like number of dependents). And this stuff needs to be retained forever (until years after the person dies) but not necessarily in a single table. Perhaps a different tables for each year??? With a view to pull them together for reporting.. So your question is how to generate 27000 rows for 20111101 similar to 27000 rows for 20101001 and you're worried about the client/server transfers and client memory of 27000 rows. - this doesn't need to be a client process - the client can kick off a stored procedure in SQL Server.to generate all 27000 rows. Phil
    50,860 pointsBadges:
    report
  • philpl1jb
    So your question is how to generate 27000 rows for 20111101 similar to 27000 rows for 20101001 <<-- this should have been 20111001 --> and you’re worried about the client/server transfers and client memory of 27000 rows. - this doesn’t need to be a client process - the client can kick off a stored procedure in SQL Server.to generate all 27000 rows.
    50,860 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following