Home

PowerForce

Introduction

This document is a quick outline of Stage I of our SQL migration, specifically the creation of table CODES_REF.

 

 

Section

Powerforce has too many 1 or 2 record tables created by inept developers without the foresight to consolidate into one central CODES table managed through some form of category key.

In order to migrate to SQL, we have over 60 such table which need to be marshalled across into SQL.

Some of these are quite simple CODE : DESCRIPTION combinations, and shall be merged into the SQL CATEGORIES & CODES files.

Where the OI record is effectively a codes record, however it has one or more other fields, rather than further polute the CODES file, we shall use CODES_REF & store the OI record in its raw form as detailed below.

 

Top of Page

CATEGORIES & CODES

USE [nvzn11]
GO

/****** Object: Table [dbo].[CATEGORIES] Script Date: 03/17/2012 12:18:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CATEGORIES](
[id] [nchar](10) NOT NULL,
[description] [nvarchar](50) NOT NULL,
[type] [char](10) NOT NULL,
[access_level] [nchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

USE [nvzn11]
GO

/****** Object: Table [dbo].[CODES] Script Date: 03/17/2012 12:19:35 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CODES](
[id] [varchar](20) NOT NULL,
[description] [varchar](50) NOT NULL,
[user_1] [varchar](256) NULL,
[category] [varchar](10) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Top of Page

CODES_REF

USE [nvzn11]
GO

/****** Object: Table [dbo].[CODES_REF] Script Date: 03/17/2012 12:08:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CODES_REF](
[_id] [int] IDENTITY(1,1) NOT NULL,
[id] [varchar](20) NOT NULL,
[description] [varchar](256) NOT NULL,
[category] [varchar](10) NOT NULL,
[user_1] [varchar](256) NULL,
[user_2] [varchar](max) NULL,
[user_3] [varchar](max) NULL,
[image_1] [varbinary](max) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

The _id istesting only to identify the value of such a field in this table. I don't imagine it will have any use as the [id] field is the OI @ID equivalent.

Top of Page

Section

XX

Top of Page

See Also