Active' Flag or Not

`active' flag or not?

You partition the table on the active flag, so that active records are in one partition, and inactive records are in the other partition. Then you create an active view for each table which automatically has the active filter on it. The database query engine automatically restricts the query to the partition that has the active records in it, which is much faster than even using an index on that flag.

Here is an example of how to create a partitioned table in Oracle. Oracle doesn't have boolean column types, so I've modified your table structure for Oracle purposes.

CREATE TABLE people
(
   id       NUMBER(10),
   name     VARCHAR2(100),
   active   NUMBER(1)
)
PARTITION BY LIST(active)
(
   PARTITION active_records VALUES (0)
   PARTITION inactive_records VALUES (1)
);

If you wanted to you could put each partition in different tablespaces. You can also partition your indexes as well.

Incidentally, this seems a repeat of this question, as a newbie I need to ask, what's the procedure on dealing with unintended duplicates?

Edit: As requested in comments, provided an example for creating a partitioned table in Oracle

React - one state for managing 'active' flag on multiple compontents

TLDR; react router v4 NavLink perform singular maintenance of one active className, dependant on the current routed page.

NavLink property 'activeClassName' will parse a specfied className to the current routed page, whilst removing on non-routed pages. All state management in this question is therefore redundant.

Python 3.9.6 - Trying to set flag to False. Keep getting error TypeError: '' not supported between instances of 'str' and 'int'

If you want to keep the active flag, you have to avoid the line:

if age < 3:

Since age is now a string (equal to 'quit'). Try this:

active = True

while active:
    age = input('Enter age for ticket price: ')
    if age == 'quit':
        active = False
    else:
        age = int(age)

        if age < 3:
            print("You get in free!")
        elif age < 13:
            print("Your ticket is £10.")
        elif age > 13:
            print("Your ticket is £15.")

Using *ngIf to Handle Active vs Inactive Flags Being Printed to View in Angular 2 App

You might want to look at ngSwitch.

It's like a switch statement just handled in templating. You might have to create another variable to control the switch statement. Hopefully you can handle that logic, as I'm just passing along the syntactic construct it seems you might be looking for.

Update record with IsActive

You could do this yourself, but I would give Temporal Tables a try first, this is pretty much exactly what they were designed for. You just need to learn slightly different query syntax to find the rows that were "active" at a point in time or during a range.

In the meantime I can share a very simple example of how to do this yourself, but I agree with the comment that Name + Surname + Age is a terrible primary key, because all three of those could change for any given employee (and the age absolutely will change - this is why we usually store Birthdate instead). Why don't we pretend that we assign each employee, somewhere, a unique but meaningless ID (the kind of thing that would go on their access badge or the data in the HR database) just to keep the example simple.

Let's say your table is:

CREATE TABLE dbo.EmployeeHistoryStuff
(
  ID            bigint       NOT NULL IDENTITY(1,1) PRIMARY KEY,
  EmployeeID    int, 
  Name          nvarchar(50), 
  Surname       nvarchar(50),
  Age           tinyint,
  IsActive      bit          NOT NULL DEFAULT (1),
  EffectiveDate datetime2(0) NOT NULL DEFAULT sysutcdatetime()
);

And we can add a few sample rows:

INSERT dbo.EmployeeHistoryStuff(EmployeeID, Name, Surname, Age) 
VALUES(1, N'Aaron', N'Bertrand', 29),
      (2, N'Teemu', N'Selanne',  31),
      (3, N'Bobby', N'Orr',      62),
      (4, N'Wayne', N'Gretzky',  55);

Now we can create a trigger that intercepts any updates any previous rows to IsActive = 1 and inserts a new row:

CREATE TRIGGER dbo.InsteadOfEmployeeHistoryStuff
ON dbo.EmployeeHistoryStuff
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @now datetime2(0) = sysutcdatetime();
  
  UPDATE old SET IsActive = 0
    FROM inserted AS i
    INNER JOIN dbo.EmployeeHistoryStuff AS old
    ON i.EmployeeID = old.EmployeeID
    WHERE old.IsActive = 1;
  
  INSERT dbo.EmployeeHistoryStuff(EmployeeID, Name, Surname, Age, EffectiveDate)
    SELECT EmployeeID, Name, Surname, Age, DATEADD(SECOND, 1, @now)
      FROM inserted;
END
GO

Now, if we perform an update because some employees had a birthday:

 UPDATE dbo.EmployeeHistoryStuff SET Age += 1 WHERE EmployeeID IN (1,2);

ID	EmployeeID	Name	Surname	Age	IsActive	EffectiveDate
1	1	Aaron	Bertrand	29	False	2021-10-22 13:37:24
2	2	Teemu	Selanne	31	False	2021-10-22 13:37:24
3	3	Bobby	Orr	62	True	2021-10-22 13:37:24
4	4	Wayne	Gretzky	55	True	2021-10-22 13:37:24
5	1	Aaron	Bertrand	30	True	2021-10-22 13:37:30
6	2	Teemu	Selanne	32	True	2021-10-22 13:37:30

---

Related Topics

How to Enforce Set-Like Uniqueness Between Multiple Columns

Select Newest Record Group by Username in SQL Server 2008

Find N Nearest Neighbors for Given Point Using Postgis

SQL Explain Plan: What Is Materialize

How to Check Type of Value in Postgres

How to Import a SQL File into a Rails Database

SQL Management Studio Won't Recognize a Table Exists After Scripted Create

How to Describe Table in SQL Server 2008

SQL Server Output Parameter Issue

How to Select Top X But Still Get a Count of the Whole Query

How to Show Blank Record in SQL If Duplicate Rows Exists

Insert Default Value When Parameter Is Null

Retrieve the Maximum Length of a Varchar Column in SQL Server

Delete Statement in SQL Is Very Slow

How to Think in SQL

Index for Multiple Columns in Activerecord

Postgresql - Repeating Rows from Limit Offset

Copy from One Database to Another Using Oracle SQL Developer - Connection Failed

---

Leave a reply

Submit