Systems Engineering and RDBMS

Design issue and a SQL Solution

Posted by decipherinfosys on November 6, 2008

How many times have you ended up in a scenario where the design of the schema was not optimal and were told that you have to fix the issue without changing the schema? And the resistance to changing the schema is still there despite telling folks about the performance implications of patching up bad design with code that should not be needed. We have highlighted such design issues off and on in our blog posts in the past.

Today also we will look at another such 2 issues:

Issue #1: In an OLTP system, keeping the aggregated quantity at the header level i.e. there is a HDR table and a DTL table and the quantity is at the DTL level. However, the system maintains an AGG_QTY column in the HDR table which needs to be updated anytime an insert/update/delete happens for the detail records. To make matters worse, at times we have seen different applications writing against that schema and not keeping it up to date which makes an inventory mis-match. Relational design for transactional applications should be done such that doing an easy join and aggregation should be a READ operation and not a WRITE operation. In this case, there was no need to store the aggregated information at the HDR level. Well, can’t change the design. Need a solution to fix the issue. What would you do? Trigger? Yup. Another alternative, if the client is ok with it is to have a view that presents the roll up information but then again, it will be a code change to read that information from the view instead of the table.

Issue #2: Kind of the same scenario but with a “twist”. There is a table say TABLEA which has say three columns: COL1, COL2 and COL3 which together make up the alternate key for that table. For a given combination of COL1 and COL2, the client wanted at the most 3 enteries. So:

COL1 COL2 COL3
A1 B1 10
A1 B1 20
A1 B1 30

If we try to create another record with:

COL1 COL2 COL3
A1 B1 40

It should give an error even though the alternate key (by design) allows it. There are many ways of achieving this – trigger is an obvious one…in the case of Oracle, one can do this via a materialized view as well and then creating a check constraint on it. Example:

SQL>
SQL> create materialized view log on TABLEA with rowid(COL1, COL2) including new values;

SQL> create materialized view MV_TEST
2 refresh fast
3 on commit
4 as
5 select COL1, COL2, count(*) CNT
6 from TABLEA
7 group by COL1, COL2
8 /

SQL> alter table MV_TEST add constraint check_val_3 check(CNT<=3);

And here is a simple trigger definition for this (SQL Server Syntax):

CREATE TABLE TABLEA (COL1 NVARCHAR(10), COL2 NVARCHAR(10), COL3 INT);
CREATE UNIQUE INDEX TABLEA_IND_1 ON TABLEA (COL1, COL2, COL3);
INSERT INTO TABLEA VALUES (‘A1’, ‘B1’, 10);
INSERT INTO TABLEA VALUES (‘A1’, ‘B1’, 20);
INSERT INTO TABLEA VALUES (‘A1’, ‘B1’, 30);

/*Trigger Definition*/
create trigger dbo.test_allow_unique
on TABLEA
for insert, update
AS
set nocount on

IF EXISTS(select 1
from inserted as i
inner join dbo.TABLEA as tu
on i.col1 = tu.col1
and i.col2 = tu.col2
group by tu.col1, tu.col2
having count(*) > 3)
BEGIN
ROLLBACK
RAISERROR(‘Uniqueness Criteria violated.’, 16, 1)
END
GO

Now, when you try to insert another value:

INSERT INTO TABLEA VALUES (‘A1’, ‘B1’, 40);

You will get the error:

Msg 50000, Level 16, State 1, Procedure test_allow_unique, Line 18
Uniqueness Criteria violated.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Or if you are using SQL Server 2008, you can use filtered indexes as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: