SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 advice on archiving design
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mahdi87_gh
Yak Posting Veteran

Iran
72 Posts

Posted - 09/07/2013 :  03:07:52  Show Profile  Reply with Quote
Hi
I have a table which named "RawNews" with the following fields:
[NewsID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
[Title] [nvarchar](200) NULL,
[Description] [nvarchar](500) NULL,
[Text] [ntext] NULL,
[RegDate] [nvarchar](50) NULL,
[RegTime] [time](0) NULL,
[Status] [nvarchar](300) NULL,
[Tags] [nvarchar](50) NULL,
[SecurityLevelID] [smallint] NULL,
[IsDeleted] [bit] NULL,
[DelDate] [nchar](10) NULL,
[UserName] [nvarchar](50) NULL,

and another table named "UsedNews" which has the same fields plus
some other fields.
and there are some other tables realted to these tables like uploads,images,newsGroups,NewsRooms,Users and etc.


in the RawNews I have 100 records each day. and these 100 recors for UsedNews.
these information should be kept for like eternity.
I wanted to ask you to advice me for a good way too make archive of records, so the search and filtering performance remains good.
thanks a lot

mahdi87_gh
Yak Posting Veteran

Iran
72 Posts

Posted - 09/07/2013 :  03:28:22  Show Profile  Reply with Quote
and another question: I have log table which logs every event in the system, should i keep it in a seperate database or not?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 09/07/2013 :  08:42:30  Show Profile  Reply with Quote
If you get only 100 records a day, that is only 36,500 records a year, which is very small. So you wouldn't need to archive them or do anything special to split the data into live vs archive at least for a few years. Are you running into any performance problems? If you are, appropriate maintenance on the database along with the correct indexes and efficient queries would help.

Usually I keep the audit logs in the same database as the one where I have the data. But that is my choice for my particular situation. How you should design yours depends on the specifics of your problem. Unless there is any compelling reason to move them off to another database, I would keep it in the same database.
Go to Top of Page

mahdi87_gh
Yak Posting Veteran

Iran
72 Posts

Posted - 09/07/2013 :  10:14:49  Show Profile  Reply with Quote
thanks a lot.
we have an old news website which I'm going to upgrade it to a new design. so I'm open handed here and i'll look into new ideas.
so if we have 36500 X 2 = 73000 records per year, it would be 730000 record after 10 years. in my program there is date picker which user picks date and sees that date's news-es. so you think there would be no performance issues?

thanks again.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 09/07/2013 :  10:33:06  Show Profile  Reply with Quote
Yep - no performance issues here with that small amount of data.
You might want to review your "used news" to refer back to the original rather than a copy. Also store your date as a date not a string.
Also read up on portioning and conditional indexes if you're worried (you need not be).
Go to Top of Page

mahdi87_gh
Yak Posting Veteran

Iran
72 Posts

Posted - 09/07/2013 :  10:48:08  Show Profile  Reply with Quote
Ok,thanks.
I'm using Persian Calender, so i have to store my date as string. is there any other solutions? if i store it as date, i have to convert it in any query.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 09/07/2013 :  19:24:12  Show Profile  Reply with Quote
Ah ok. Unfortunately SQL Server is a bit parochial in this regard.
There are a few options but it would depend on what you're likely to do with the dates as to which (if any) are best. As dates are stored as numbers, you should be able to use the basics <,> = etc. without a problem. It's when you want intervals, parts of date and stuff where it gets tricky but you're no better off with a string. Consider using a date and converting it to Persian in your client/front end.
At the end of the day, you know best though.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000