Year wise Data Storage Design

Last Post 10 Feb 2006 01:17 AM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
amansbains
New Member
New Member

--
05 Feb 2006 10:02 PM
I need some help regarding designing a new Database which will be designed for Financial Accounting also.
I thought it would be a good idea to break up the database design in such a way, so as to keep each Financial Year Data seperately. This would make backups faster and required backup storage space lesser.

If anyone can suggest the basics of such a design it would be really helpful.
SQLUSA
New Member
New Member

--
10 Feb 2006 01:17 AM
Yes. It is a good idea.

It is called horizontal partitioning. The downside: you need the queries to be cognizant of the partitioning.

The result is much faster operation. You don't have to trip over past data constantly.

SQL 2005 has partitioning built in, whereby the query does not have to know about the partitioning at all.

Kalman Toth, Database Architect
http://www.sqlusa.com/orderdoubleheader/
The Best SQL Server 2005 Training in the World
pyale
New Member
New Member

--
03 Mar 2006 05:08 AM
If you use a partitioned view, then the code doesn't need to be aware of the paritioning (in SQL2000).

I have a table which logically contains 2.5 billion rows. To make this manageable, the data is split into separate tables, one for each calendar month, and a view sits on top of all these tables, as follows:

CREATE TABLE [pf_data_store_2005_01] (
[pf_data_store_id] [bigint] NOT NULL ,
[unit_id] [int] NOT NULL ,
[dt_gps] [datetime] NOT NULL ,
[latitude] [float] NOT NULL ,
[longitude] [float] NOT NULL ,
[speed_kph] [float] NOT NULL ,
[data_feed_id] [tinyint] NOT NULL ,
[dt_inserted] [datetime] NOT NULL ,
[point_type] [int] NOT NULL ,
[dt_utc] [datetime] NOT NULL ,
CONSTRAINT [pk_pf_data_store_2005_01] PRIMARY KEY CLUSTERED
(
[dt_gps],
[unit_id],
[pf_data_store_id]
) ON [Data] ,
CONSTRAINT [ck_pf_data_store_2005_01_dt_gps]
CHECK ([dt_GPS] >= '01 JAN 2005' and [dt_GPS] < '01 FEB 2005')
) ON [Data]
GO

This is repeated for each month, with appropriate table names and check constraint values.

A partitioned view then references all these tables:

ALTER VIEW v_pf_data_store
AS
SELECT * FROM pathfinder_data_store..pf_data_store_2005_01
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_02
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_03
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_04
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_05
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_06
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_07
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_08
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_09
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_10
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_11
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2005_12
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2006_01
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2006_02
UNION ALL
SELECT * FROM pathfinder_data_store..pf_data_store_2006_03


When inserting data, inserting into the VIEW ensures that the data is automatically placed in the correct table (by virtue of the check constraints). Likewise, when selecting data from the view, all retrieval is done from the table(s) whose valid ranges include the range being requested - all other tables are ignored.
amansbains
New Member
New Member

--
13 Mar 2006 01:33 AM
Thanks your suggestion makes the job EXTREMELY EASY
You are not authorized to post a reply.

Acceptable Use Policy