Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Plan to build read-only database Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 8:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 12, 2014 3:37 PM
Points: 41, Visits: 496
Hi,

I have a plan to built read-only database. This database (read-only) store all payments data from another database (read and write).

Let say, I have the following database
1. payment ~ user can read, insert, delete and update
2. payment_2012 ~ user only can read

In payment database, I've the following table
CREATE TABLE [dbo].[paymentH](
[idx] [int] IDENTITY(1,1) NOT NULL,
[paymentId] [varchar](100) NOT NULL,
[paymentDte] [datetime] NOT NULL,
CONSTRAINT [PK_paymentH] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [paymentH_UQ1] UNIQUE NONCLUSTERED
(
[paymentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[paymentD] Script Date: 10/17/2012 21:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[paymentD](
[idx] [int] IDENTITY(1,1) NOT NULL,
[paymentId] [varchar](100) NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_paymentD] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: ForeignKey [FK_paymentD_paymentH] Script Date: 10/17/2012 21:55:01 ******/
ALTER TABLE [dbo].[paymentD] WITH CHECK ADD CONSTRAINT [FK_paymentD_paymentH] FOREIGN KEY([paymentId])
REFERENCES [dbo].[paymentH] ([paymentId])
GO
ALTER TABLE [dbo].[paymentD] CHECK CONSTRAINT [FK_paymentD_paymentH]
GO



In my payment_2012 database, I've the following table,
CREATE TABLE [dbo].[allPayment](
[idx] [int] IDENTITY(-2147483648,1) NOT NULL,
[paymentId] [varchar](100) NOT NULL,
[paymentDte] [datetime] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_allPayment] PRIMARY KEY CLUSTERED
(
[idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Explanation is as follows,
1. Data for all payments for the year 2012 in payment database will be extracted, transform and load into payment_2012 database
2. allPayment table in payment_2012 database , deliberately not normalized
3. allPayment table in payment_2012 database is not normalized because
- data is read-only
- I will be able to read it quickly

my question is as follows,
1. Is it my explanation for (2) and (3) as above is true?

need comment / idea from expert
Post #1373808
Posted Thursday, October 18, 2012 4:19 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:49 AM
Points: 739, Visits: 2,470
1. Why have date and amount in seperate tables?
2. If paymentId is unique it is possibly a good candidate for a primary key, especially if it is ascending.
3. Is paymentId data type correct?

CREATE TABLE [dbo].[Payment](
[paymentId] [varchar](100) NOT NULL,
[paymentDte] [datetime] NOT NULL,
[amt] [decimal](10, 2) NOT NULL,
CONSTRAINT [PK_paymentH] PRIMARY KEY CLUSTERED ([paymentId] ASC)
) ON [PRIMARY];
GO

This structure would then work in both the read\write and read only databases.




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1374251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse