How Can I Efficiently Generate Unique Serial Numbers for Address Changes within Person Groups in a Database?

Streamlining Address Change Tracking with Database Serial Numbers

Managing address changes within a database often requires a history table to record each modification. This table typically includes a person ID, a sequence number for each address change, a timestamp, and the previous address. However, automatically incrementing the sequence number per person can be surprisingly complex.

Simplifying the Approach: Avoid Auto-Increment Constraints

Instead of using auto-incrementing sequences or constraints—which often lead to convoluted solutions—a simpler method involves a standard serial (auto-increment) or IDENTITY column:

<code class="language-sql">CREATE TABLE address_history (
  address_history_id serial PRIMARY KEY,
  person_id INT NOT NULL REFERENCES people(id),
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  previous_address TEXT
);</code>

Generating Serial Numbers with Window Functions

With this table structure, window functions offer an elegant solution for generating sequential numbers for each person without gaps:

<code class="language-sql">CREATE VIEW address_history_nr AS
SELECT *, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY address_history_id) AS adr_nr
FROM address_history;</code>

This view acts as a direct replacement for the original table, providing the desired serial numbers (adr_nr) within your queries.

Data Model Optimization for Efficiency

For optimal performance, consider these data model improvements:

• Use TIMESTAMP WITHOUT TIME ZONE (or TIMESTAMP WITH TIME ZONE) for precise time tracking instead of DATETIME.
• Minimize data storage; the previous_address field is sufficient, eliminating the need for separate address and original_address columns. This reduces storage space and improves query speed.

The above is the detailed content of How Can I Efficiently Generate Unique Serial Numbers for Address Changes within Person Groups in a Database?. For more information, please follow other related articles on the PHP Chinese website!