Store
Key – New RDBMS Concept in MS SQL SERVER 2000
Here is a new RDBMS concept, “Store Key” alias
“SK”. In simple terms you store all the
redundant data of a database in one table and use it whenever/wherever required
via “Store Key ID” alias “SKID”.
This article targets the redundant data as well
as RDBMS Normalization concept. The
base idea of normalization is storing the redundant data in another table and
creating a link between the divided tables.
Normally we store the redundant data in Table X and have the Foreign Key
relation to the actual column in Table Y.
The SK concept is simple, instead of creating one table per one such
relation; create a single table for a database, store all the redundant data
there. This reduces the
cost/maintenance of tables, Primary Keys, Indexes and etc.
Let us call the table that stores redundant
data as “SKTABLE”.
SKTABLE Structure:
|
Column Name
|
Data Type
|
Description
|
|
SKID
|
INT
[IDENTITY COLUMN]
|
Store Key ID. Unique identification number of the Data column
|
|
DATA
|
VARCHAR
|
The actual data
|
By creating Stored
Procedures/functions/triggers, we can deal with data transaction between actual
table columns and SKTABLE data.
Let us take up couple of examples to show how
SK can be implemented. In both the
examples “INSTEAD OF Trigger” feature SQL SERVER 2000 is used. Note that I am focusing on the SK concept
rather than the way of achieving it, there might be better ways of doing it.
Examples
Example 1: This targets redundant data. Assume that you have two tables Candidate
and Employer, both stores address of Candidates and Companies
respectively. Both the tables include
Address1, Address2 columns. Let us
apply Store Key concept for this.
The UPDATE and DELETE SQL is not handled in
this example.
Script:
--SKTable Table
IF EXISTS (SELECT
* FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[SKTABLE]')
AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
TRUNCATE TABLE DBO.SKTABLE
ELSE
CREATE TABLE DBO.SKTABLE
(SKID int IDENTITY (1,1) NOT NULL,
DATA varchar(2000) NULL)
GO
--Candidate
Table
CREATE TABLE DBO.CANDIDATE
(CANDIDATENAME VARCHAR(25),
ADDRESS1 VARCHAR(50),
ADDRESS2 VARCHAR(50))
--Employer Table
CREATE TABLE DBO.EMPLOYER
(EMPLOYERNAME VARCHAR(25),
ADDRESS1 VARCHAR(50),
ADDRESS2 VARCHAR(50))
--Trigger on Candidate
Table
CREATE TRIGGER DBO.TSKCAND ON DBO.CANDIDATE INSTEAD OF
INSERT AS
BEGIN
--Variable to store the Unique ID of
Data
DECLARE @sSKID AS CHAR(10)
--Check the inserting data already
exist in Store Key Table
SELECT @sSKID = LTRIM(RTRIM(STR(SKID))) FROM DBO.SKTABLE
WHERE DATA IN (SELECT ADDRESS2 FROM INSERTED)
IF @@ROWCOUNT = 0
BEGIN
--If the Data being inserted
does not exist in SKTable, create one
INSERT INTO DBO.SKTABLE
(DATA) SELECT ADDRESS2 FROM
INSERTED
SELECT @sSKID = LTRIM(RTRIM(STR(MAX(SKID)))) FROM DBO.SKTABLE
END
INSERT DBO.CANDIDATE(CANDIDATENAME,
ADDRESS1, ADDRESS2)
SELECT CANDIDATENAME, ADDRESS1,
@sSKID FROM INSERTED
END
--Trigger on
Employer table
CREATE TRIGGER DBO.TSKEMP ON DBO.EMPLOYER INSTEAD OF
INSERT AS
BEGIN
--Variable to store the Unique ID of
Data
DECLARE @sSKID AS CHAR(10)
--Check the inserting data already
exist in Store Key Table
SELECT @sSKID = LTRIM(RTRIM(STR(SKID))) FROM DBO.SKTABLE
WHERE DATA IN (SELECT ADDRESS2 FROM INSERTED)
IF @@ROWCOUNT = 0
BEGIN
--If the Data being inserted
does not exist in SKTable, create one
INSERT INTO DBO.SKTABLE
(DATA) SELECT ADDRESS2 FROM
INSERTED
SELECT @sSKID = LTRIM(RTRIM(STR(MAX(SKID)))) FROM DBO.SKTABLE
END
INSERT DBO.EMPLOYER(EMPLOYERNAME,
ADDRESS1, ADDRESS2)
SELECT EMPLOYERNAME, ADDRESS1,
@sSKID FROM INSERTED
END
Now,
insert some rows and see how Store Key concept works.
--FOLLOWING 2
SQLS CREATES 2 ENTRIES IN SKTABLE
INSERT INTO DBO.CANDIDATE
(CANDIDATENAME, ADDRESS1, ADDRESS2)
VALUES ('RAJEEV RAO', '#15, 1ST
CROSS', 'BANASHANKARI')
INSERT INTO DBO.CANDIDATE
(CANDIDATENAME, ADDRESS1, ADDRESS2)
VALUES ('SANTOSH XAVIER', '#30,
3RD CROSS', 'MG ROAD')
--FOLLOWING 2
SQLS USES WILL NOT CREATE ENTRIES IN SKTABLE
--AS IT ADDRESS2
ENTRIES EXISTS IN SKTABLE. IT JUST
INSERTS SKID REFERENCE.
INSERT INTO DBO.EMPLOYER (EMPLOYERNAME,
ADDRESS1, ADDRESS2)
VALUES ('THOMSON FINANCIAL',
'#15, BAHAIS BHAVAN ROAD', 'MG ROAD')
INSERT INTO DBO.EMPLOYER (EMPLOYERNAME,
ADDRESS1, ADDRESS2)
VALUES ('WORLDSCOPE DISCLOSURE',
'100, DVG ROAD', 'BANASHANKARI')
--FOLLOWING SQL
CREATES AN ENTRY IN SKTABLE AS ADDRESS2 IS NEW HERE
INSERT INTO DBO.EMPLOYER (EMPLOYERNAME,
ADDRESS1, ADDRESS2)
VALUES ('PRIMARK
INDIA PVT LTD', '25, 5TH MAIN', 'KORAMANGALA')
<--[if gte vml 1]>
<--[if gte mso 9]>
Example 2: This targets Normalization concept. Assume that you have Employee and Department
details in one table; you decided to normalize it to two tables i.e. Employee
and Department. Let us apply Store Key
concept for this instead dividing it into two tables.
The Employee table stores Department name also
with other information about employee, by making Department Name column as SK
column we can store Department Names in SKTable and have SKID reference in
Employee table.
Add/Edit the entries in Employee table that in
turn add/edit respective entries in SKTABLE as well.
The DELETE SQL is not handled in this example.
Script:
Note: The script creates objects under DBO
owner. Change it if required.
--SKTable table
IF EXISTS (SELECT
* FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[SKTABLE]')
AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
TRUNCATE TABLE DBO.SKTABLE
ELSE
CREATE TABLE DBO.SKTABLE
(SKID int IDENTITY (1,1) NOT NULL,
DATA varchar(2000) NULL)
GO
--Employee table
CREATE TABLE DBO.Employee (
EMPCODE char (10) NOT NULL,
EMPNAME varchar (25) NULL,
DEPTCODE char (10) NULL --Store Key Column
)
GO
--Trigger on
Employee table
CREATE TRIGGER DBO.TSKDEPT ON DBO.EMPLOYEE INSTEAD OF
INSERT, UPDATE AS
BEGIN
--Variable to store the Unique ID of
Data
DECLARE @sSKID AS CHAR(10)
--Variable to identify the
transaction as UPDATE/INSERT
DECLARE @IUPSERT AS INT
--If Deleted table contains 0 rows
then it is an insert sql
SELECT @IUPSERT = COUNT(1) FROM DELETED
--Check the inserting data already
exist in Store Key Table
SELECT @sSKID = LTRIM(RTRIM(STR(SKID))) FROM DBO.SKTABLE
WHERE DATA IN (SELECT DEPTCODE FROM INSERTED)
IF @@ROWCOUNT = 0
BEGIN
--If the Data being inserted
does not exist in SKTable, create one
INSERT INTO DBO.SKTABLE (DATA)
SELECT DEPTCODE FROM INSERTED
SELECT @sSKID = LTRIM(RTRIM(STR(MAX(SKID)))) FROM DBO.SKTABLE
END
--Triggered for Insert SQL
IF @IUPSERT = 0
BEGIN
INSERT DBO.EMPLOYEE(EMPCODE,
EMPNAME, DEPTCODE)
SELECT EMPCODE, EMPNAME,
@sSKID FROM INSERTED
END
ELSE
--Triggered for Update SQL
BEGIN
UPDATE DBO.EMPLOYEE
SET EMPCODE =
INSERTED.EMPCODE, EMPNAME = INSERTED.EMPNAME, DEPTCODE = @sSKID
FROM DBO.EMPLOYEE,
INSERTED
WHERE DBO.EMPLOYEE.EMPCODE
= INSERTED.EMPCODE AND DBO.EMPLOYEE.EMPNAME = INSERTED.EMPNAME
END
END
Now insert some rows and see how Store Key
concept works. The beauty here is you
just need to insert the actual value itself e.g. if the department name is
‘PRODUCTION’ insert it as it is, Store Key takes care of allocating SKID and
storing the SKID instead of ‘PRODUCTION’.
--THE FOLLOWING
3 INSERT STATEMENT CREATES 3 ROWS IN EMPLOYEE AND SKTABLE TABLES
INSERT INTO DBO.EMPLOYEE (EMPCODE,
EMPNAME, DEPTCODE)
VALUES
('EMP1', 'BHASKER SATYAMURTHY', 'TECHNOLOGY')
INSERT INTO DBO.EMPLOYEE (EMPCODE,
EMPNAME, DEPTCODE)
VALUES ('EMP2', 'JOSEPH JEUNE',
'ACCOUNTS')
INSERT INTO DBO.EMPLOYEE (EMPCODE,
EMPNAME, DEPTCODE)
VALUES ('EMP3', 'VIJAY SHARMA',
'PRODUCTION')
--THE FOLLOWING
2 INSERT STATEMENT WILL NOT CREATE ROW IN SKTABLE
--AS THE
DEPTCODE WE ARE GIVING ALREADY EXIST
INSERT INTO DBO.EMPLOYEE (EMPCODE,
EMPNAME, DEPTCODE)
VALUES ('EMP4', 'SUNIL KUMAR',
'PRODUCTION')
INSERT INTO DBO.EMPLOYEE (EMPCODE,
EMPNAME, DEPTCODE)
VALUES ('EMP5',
'RAGHAVENDRA UPADHYA', 'TECHNOLOGY')
Now
update “DEPTCODE” column for two rows.
--THIS UPDATE
STATEMENT UPDATES DEPTCODE OF 'JOSEPH JEUNE'
--FROM ACCOUNTS
TO PRODUCTION
UPDATE DBO.EMPLOYEE SET DEPTCODE = 'PRODUCTION' WHERE
EMPCODE = 'EMP2'
--THIS UPDATE
STATEMENT INSERTS NEW DEPTCODE IN SKTABLE
--AS 'HR' DOES
NOT EXIST IN SKTABLE
UPDATE DBO.EMPLOYEE SET DEPTCODE = 'HR'
WHERE EMPCODE
= 'EMP5'
Conclusion
Store Key concept has a broader usability with
respect to reducing maintenance of many tables, primary keys, indexes in those
tables and etc. The base idea that I though was creating a Store Room kind of
repository for a database and store any kind of redundant data there, use it
via SK ID wherever required. Rather
than we writing some database objects for achieving Store Key concept, it would
be better that MS SQL SERVER 2000 provide some inbuilt system functionalities
for storing/manipulating Store Key Data. Please feel free to comment/criticise
the article.
- Raghavendra Narayana
Download article