Plan to build read-only database
-
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
-
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply