SQL – Referential integrity

referential-integritysqlite

seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question:
I have three tables or more, refer to those as A,B,C.
C holds some columns and A & B refer to those columns.
A & B data can be deleted -> and here comes my question:
I'd like the referential to go as when there are no valid references to C, the referenced row will get deleted, since it's not in use.
I thought of adding an auto_increment to C, and make A & B refer to the index, and just keep multiple of the same data, and just delete the attached. This hardly seems like the most reasonable solution.
If anyone could point some light on my problem, that would be great.
Thank you.

EDIT: I'm using SQLite.

Best Answer

I would implement it using a reference counter:

CREATE TRIGGER new_in_b AFTER INSERT ON B
FOR EACH ROW 
BEGIN
  UPDATE A SET refcounter = refcounter + 1 WHERE id = new.foreignkey;
END;

CREATE TRIGGER remove_from_b AFTER DELETE ON B
FOR EACH ROW 
BEGIN
  UPDATE A SET refcounter = refcounter - 1 WHERE id = old.foreignkey;
  DELETE FROM A WHERE id = old.foreignkey AND refcounter = 0;
END;

Related Solutions

Mysql – How should an object that can reference an object of multiple types be modeled in a relational schema

If the relationship is strictly hierarchical (i.e. a portfolio can have several sub-portfolios or projects, but a project cannot appear in more than one sub-portfolio) then you can model portfolio and project using a subtype pattern, e.g.

-- === PortfolioItem  table ===========================================
-- Supertype table that records the relationships between portfolio 
-- items.
create table PortfolioItem (
       PortfolioItemID      int not null
      ,PortfolioItemRef     varchar (20) not null 
      ,PortfolioItemType    varchar (10) not null -- either 'PROJ' or 'PORT'
      ,ParentItemID         int -- Null for root
)
go

alter table PortFolioItem
  add constraint PK_PortfolioItem
      primary key nonclustered (PortfolioItemID)
go

alter table PortfolioItem
  add constraint UQ_PortfolioItem
      unique nonclustered (PortfolioItemRef, PortfolioItemType)
go


-- Parent-child relationship
--
alter table PortfolioItem
  add constraint FK_PortfolioItem_Parent
      foreign key (ParentItemID)
      references PortfolioItem
go



-- === PortfolioSubType ===============================================
-- This subclass table joins against the unique identifier but the 
-- check constraint restricts it to joining against 'portfolio' nodes
--
create table PortfolioSubType (
       PortfolioItemRef      varchar (20) not null
      ,PortfolioItemType     varchar (10) not null
      -- Portfolio attributes
)

alter table PortfolioSubType
  add constraint PK_PortfolioSubType
      primary key nonclustered (PortfolioItemRef, PortfolioItemType)
go

-- Cab only join against portfolio parent items
--  
alter table PortfolioSubType
  add constraint CK_Portfolio_Type
      check (PortfolioItemType = 'PORT')    
go

alter table PortfolioSubType
  add constraint FK_PortfolioSubType_SuperType
      foreign key (PortfolioItemRef, PortfolioItemType)
      references PortfolioItem (PortfolioItemRef, PortfolioItemType)
go


-- === ProjectSubType =================================================
-- This subclass table has the project specific items and a check 
-- constraint that prevents it from joining against parent nodes
-- that represent portfolios
--
create table ProjectSubType (
       PortfolioItemRef      varchar (20) not null
      ,PortfolioItemType     varchar (10) not null
      -- Project attributes
)

alter table ProjectSubType
  add constraint PK_ProjectSubType
      primary key nonclustered (PortfolioItemRef, PortfolioItemType)
go

-- Check constraint restricts this to projects
--
alter table ProjectSubType
  add constraint CK_Portfolio_Type
      check (PortfolioItemType = 'PROJ')    
go

alter table ProjectSubType
  add constraint FK_ProjectSubType_SuperType
      foreign key (PortfolioItemRef, PortfolioItemType)
      references PortfolioItem (PortfolioItemRef, PortfolioItemType)
go

You could enforce an integrity rule that prevents a project node from having children with a trigger similar to the following:

-- === Trigger to enforce integrity ===================================
-- The trigger prevents project nodes from having children.
--
create trigger ProjectNodeIntegrity 
    on PortfolioItem
   for insert, update
as
    if exists 
        (select 1
           from PortfolioItem p_i
           join inserted i
             on i.ParentItemID = p_i.PortfolioItemID
            and p_i.PortfolioItemType = 'PROJ') begin
        raiserror ('Only portfolios may have children', 16, 1)
        rollback transaction
        return
    end
go

This will bounce attempts to insert a child under a project node.

Referential Integrity – Re-using table

I work with a database that has this solution that I outline below. I don't like it because the base table has an entry for each type and it ends up being a huge table that is slow to query.

--base table

CREATE TABLE APPLICATION_LOGGING
(
  ID                  NUMBER(9)                 NOT NULL,  --primary key
  CURRENT_USER_ID     NUMBER(9),
  EVENT_ID            NUMBER(9)                 NOT NULL,
  ENTRY_DATE          DATE                      NOT NULL,
  MESSAGE             VARCHAR2(200 CHAR)        NOT NULL,
  MESSAGE_PARAMETERS  VARCHAR2(2000 CHAR)       NOT NULL
)

--table for "Car" logging

CREATE TABLE CAR_APPLICATION_LOGGING
(
  ID       NUMBER(9)                            NOT NULL, --same as applogging ID
  CAR_ID  NUMBER(9)                            NOT NULL
)

--another child table

CREATE TABLE SHIP_APPLICATION_LOGGING
(
  ID          NUMBER(9)                         NOT NULL,
  SHIP_ID  NUMBER(9)                         NOT NULL
)

to get all the ship logs you would query for

select * from ship_application_logging ship, application_logging app
where ship.ID = app.ID

this is a clean design but if each type of thing generates ten or twenty log entries per thing you don't have to do too much to have a huge table for application_logging with a million entries. Users complain it's slow to see the activity log.

The real question is:

how many of your users need to see the activity log?
how often do they view it?
will you be doing additional logging for financial or security purposes?
do you need to log every activity a user does or just critical ones?

As @Steb says "it all depends". Your application, your users, number of transactions....

Best Answer

Related Solutions

Mysql – How should an object that can reference an object of multiple types be modeled in a relational schema

Referential Integrity – Re-using table

Related Question