locked
Database design issue connected with impossibility to delete rows RRS feed

  • Question

  • I will intentionally simplify the design of my DB and present only a couple of tables.

    I have a table of sold train tickets. This table contains fields (as foreign keys) such as ticket type, tariff plan, transporter and so on.

    Ticket type, tariff plan and transporter are the other tables.

    I don't know how to implement Update and Delete operations on these tables and I'll try to explain why.

    I can't just update a row in TicketType table because some old ticket sales will change but they can not be changed (because we can't change the type of an old sold ticket).

    The same situation arises with the other tables.

    • Moved by Tom PhillipsEditor Monday, July 2, 2012 1:39 PM Database design question (From:SQL Server Database Engine)
    Sunday, July 1, 2012 12:37 PM

Answers

  • I can't just update a row in TicketType table because some old ticket sales will change but they can not be changed (because we can't change the type of an old sold ticket).

    Instead of updating the domain tables, inactivate the old one (delete logically instead of physically) and insert an active new one.  This will allow you to preserve referential integrity of the legacy data.  Newly inserted tickets should reference only the active domain table row:

    CREATE TABLE dbo.TicketType(
    	TicketTypeID int NOT NULL
    		CONSTRAINT PK_TicketType PRIMARY KEY
    	,TicketTypeName varchar(20) NOT NULL
    	,TicketTypeDescription varchar(100) NOT NULL
    	,StatusCode char(1) NOT NULL
    		CONSTRAINT CK_TicketType_StatusCode CHECK (StatusCode IN('A', 'I'))
    		CONSTRAINT DF_TicketType_StatusCode DEFAULT 'A'
    	);
    
    CREATE UNIQUE INDEX idxf_TicketType ON dbo.TicketType(StatusCode, TicketTypeID)
    	WHERE StatusCode = 'A';
    
    INSERT INTO dbo.TicketType (TicketTypeID, TicketTypeName, TicketTypeDescription)
    	VALUES
    		(1, 'First Class', 'Original first class ticket type')
    		,(2, 'Second Class', 'Second class ticket type')
    		,(3, 'Third Class', 'Third class ticket type');
    
    CREATE TABLE dbo.Ticket(
    	TicketNumber int NOT NULL IDENTITY
    	,TicketTypeID int NOT NULL
    		CONSTRAINT FK_Ticket_TicketType FOREIGN KEY REFERENCES dbo.TicketType(TicketTypeID)
    	,DateSold datetime NULL
    	);
    
    INSERT INTO dbo.Ticket (TicketTypeID)
    	VALUES
    	(1)
    	,(1)
    	,(2)
    	,(3)
    	,(3);
    
    SELECT
    	t.TicketNumber
    	,tt.TicketTypeDescription
    FROM dbo.Ticket AS t
    JOIN dbo.TicketType AS tt ON tt.TicketTypeID = t.TicketTypeID;
    
    BEGIN TRAN;
    
    --inactivate existing first class type
    UPDATE dbo.TicketType
    SET StatusCode = 'I'
    WHERE TicketTypeID = 1;
    
    --create new first class type
    INSERT INTO dbo.TicketType (TicketTypeID, TicketTypeName, TicketTypeDescription)
    	VALUES (4, 'First Class', 'New first class ticket type');
    
    COMMIT;
    
    --create new ticket with new first class type
    INSERT INTO dbo.Ticket (TicketTypeID)
    	VALUES (4);
    GO
    
    SELECT
    	t.TicketNumber
    	,tt.TicketTypeDescription
    FROM dbo.Ticket AS t
    JOIN dbo.TicketType AS tt ON tt.TicketTypeID = t.TicketTypeID;
    GO


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Sunday, July 1, 2012 2:20 PM
    Answerer

All replies

  • Use ON DELETE CASCADE ON UPDATE CASCADE option, see example below.

    CREATE TABLE GF --GRANDFATHERS
    (
     [ID] INT NOT NULL PRIMARY KEY,
     [NAME]CHAR(1) NOT NULL 
    )
    INSERT INTO GF VALUES (1,'A')
    INSERT INTO GF VALUES (2,'B')
    INSERT INTO GF VALUES (3,'C')

    CREATE TABLE F --FATHERS
    (
     [ID] INT NOT NULL PRIMARY KEY,
     GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE CASCADE ON UPDATE CASCADE,
     [NAME]CHAR(2) NOT NULL 
    )

    INSERT INTO F VALUES (1,1,'AA')
    INSERT INTO F VALUES (2,1,'AA')
    INSERT INTO F VALUES (3,2,'BB')
    INSERT INTO F VALUES (4,2,'BB')
    INSERT INTO F VALUES (5,2,'BB')
    INSERT INTO F VALUES (6,3,'CC')

    CREATE TABLE C --CHILD
    (
     [ID] INT NOT NULL PRIMARY KEY,
     FID INT NOT NULL FOREIGN KEY REFERENCES F([ID])ON DELETE CASCADE ON UPDATE CASCADE,
     [NAME]CHAR(3) NOT NULL 
    )

    INSERT INTO C VALUES (1,1,'AAA')
    INSERT INTO C VALUES (2,1,'AAA')
    INSERT INTO C VALUES (3,2,'AAA')
    INSERT INTO C VALUES (4,3,'BBB')
    INSERT INTO C VALUES (5,4,'BBB')
    INSERT INTO C VALUES (6,4,'BBB')
    INSERT INTO C VALUES (7,5,'BBB')
    INSERT INTO C VALUES (8,5,'BBB')
    INSERT INTO C VALUES (9,5,'BBB')
    INSERT INTO C VALUES (10,6,'CCC')
    INSERT INTO C VALUES (11,6,'CCC')
    INSERT INTO C VALUES (12,6,'CCC')


    SELECT * FROM GF
    SELECT * FROM F
    SELECT * FROM C



    DROP TABLE GF
    DROP TABLE F
    DROP TABLE C


    DECLARE @I INT,@J INT,@K INT
    SET @I =0
    SET @J =0
    SET @K =0

    DELETE FROM GF WHERE ID=1
    UPDATE GF SET @I=[ID]=@I+1
    UPDATE F SET  @J=[ID]=@J+1
    UPDATE C SET @K=[ID]=@K+1

     

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Sunday, July 1, 2012 12:42 PM
  • Your example is not working. And I think you didn't understand the issue.

    When I Update the referenced table I can not modify old records in dependent table because it will lead to the situation when someone bought a ticket on 1 march of 2012 on TariffPlan1 and someday when I will modify TariffPlan table this record I mentioned above become sold on 1 march 2012 on TariffPlan2.


    Sunday, July 1, 2012 1:36 PM
  • I would understand the issue if you post CREATE TABLE+ INSERT INTO + desired result. Always state what version you are using.

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Sunday, July 1, 2012 1:50 PM
  • I can't just update a row in TicketType table because some old ticket sales will change but they can not be changed (because we can't change the type of an old sold ticket).

    Instead of updating the domain tables, inactivate the old one (delete logically instead of physically) and insert an active new one.  This will allow you to preserve referential integrity of the legacy data.  Newly inserted tickets should reference only the active domain table row:

    CREATE TABLE dbo.TicketType(
    	TicketTypeID int NOT NULL
    		CONSTRAINT PK_TicketType PRIMARY KEY
    	,TicketTypeName varchar(20) NOT NULL
    	,TicketTypeDescription varchar(100) NOT NULL
    	,StatusCode char(1) NOT NULL
    		CONSTRAINT CK_TicketType_StatusCode CHECK (StatusCode IN('A', 'I'))
    		CONSTRAINT DF_TicketType_StatusCode DEFAULT 'A'
    	);
    
    CREATE UNIQUE INDEX idxf_TicketType ON dbo.TicketType(StatusCode, TicketTypeID)
    	WHERE StatusCode = 'A';
    
    INSERT INTO dbo.TicketType (TicketTypeID, TicketTypeName, TicketTypeDescription)
    	VALUES
    		(1, 'First Class', 'Original first class ticket type')
    		,(2, 'Second Class', 'Second class ticket type')
    		,(3, 'Third Class', 'Third class ticket type');
    
    CREATE TABLE dbo.Ticket(
    	TicketNumber int NOT NULL IDENTITY
    	,TicketTypeID int NOT NULL
    		CONSTRAINT FK_Ticket_TicketType FOREIGN KEY REFERENCES dbo.TicketType(TicketTypeID)
    	,DateSold datetime NULL
    	);
    
    INSERT INTO dbo.Ticket (TicketTypeID)
    	VALUES
    	(1)
    	,(1)
    	,(2)
    	,(3)
    	,(3);
    
    SELECT
    	t.TicketNumber
    	,tt.TicketTypeDescription
    FROM dbo.Ticket AS t
    JOIN dbo.TicketType AS tt ON tt.TicketTypeID = t.TicketTypeID;
    
    BEGIN TRAN;
    
    --inactivate existing first class type
    UPDATE dbo.TicketType
    SET StatusCode = 'I'
    WHERE TicketTypeID = 1;
    
    --create new first class type
    INSERT INTO dbo.TicketType (TicketTypeID, TicketTypeName, TicketTypeDescription)
    	VALUES (4, 'First Class', 'New first class ticket type');
    
    COMMIT;
    
    --create new ticket with new first class type
    INSERT INTO dbo.Ticket (TicketTypeID)
    	VALUES (4);
    GO
    
    SELECT
    	t.TicketNumber
    	,tt.TicketTypeDescription
    FROM dbo.Ticket AS t
    JOIN dbo.TicketType AS tt ON tt.TicketTypeID = t.TicketTypeID;
    GO


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Sunday, July 1, 2012 2:20 PM
    Answerer
  • I can't just update a row in TicketType table because some old ticket sales will change but they can not be changed (because we can't change the type of an old sold ticket).

    This is a very common situation, design pattern.

    Over in the dimensional modeling group they call these "slowly changing dimensions", or something like that.

    Dan explained the general approach - each row in your ticket type (etc) "dimension" tables should have from-date and to-date columns, and/or an activeFlag field.  When a ticket type goes obsolete, you put in the to-date and mark it inactive.  It is still there to resolve foreign keys in the ticket journal "fact" table.

    Josh

    Sunday, July 1, 2012 3:42 PM
  • You can use the other solution provided: deprecating the the previous value (logically deleting it).

    But the main problem is that for TICKET TYPE, you actually are talking about two different things.  A TICKET TYPE and something that I will call a TICKET TYPE VERSION.  The version of the ticket type is the type plus something like a start date.  So you have prices or terms that change over time for the same ticket type.  I'm calling it version, but you may already have a business name for it that is better. 

    TICKET TYPE ||--------o< TICKET TYPE VERSION ||--------o<TICKET SALE

    Something like that.



    --- Love Your Data @datachick blog.infoadvisors.com

    Wednesday, July 18, 2012 5:50 PM