Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Store Key - Storing Misc Data

By Narayana Raghavendra,

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.

Total article views: 3980 | Views in the last 30 days: 3
 
Related Articles
FORUM

Insert - Exec Select statements

Insert - Exec Select statements

FORUM

Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

Creating Stored Procedure with SELECT ... inside

how to create a SP with select inside

FORUM

Using the recordset from SELECT statement in an INSERT statement, all in one Stored Procedure

Using the recordset from SELECT statement in an INSERT statement, all in one Stored Procedure

SCRIPT

Insert Update Stored Procedure for a table

Generic Script for Insert Update Stored Procedures

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones