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')
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/criticize the article.