Postgresql – DB Layout for List of aliases

database-designpostgresql

Use Case

In a ticket tracking application, there are ticket-types. A ticket type can have a (primary) slug. Sometimes, a ticket type can have old/legacy aliases for the slug.

It should be straight forward to get the matching ticket-type from a slug.

DB Model

My database model looks like this: A ticket type with N aliases for the slug.

DROP TABLE IF EXISTS ticket_type_slug_alias;
DROP TABLE IF EXISTS ticket_type;

CREATE TABLE ticket_type(
  id INTEGER PRIMARY KEY,
  slug CHARACTER VARYING(50) UNIQUE,
  verbose_name CHARACTER VARYING(64) NOT NULL
);

CREATE TABLE ticket_type_slug_alias(
  id             INTEGER PRIMARY KEY,
  ticket_type_id INTEGER,
  FOREIGN KEY(ticket_type_id) REFERENCES ticket_type(id),
  slug CHARACTER VARYING(50) UNIQUE
);

Problem 1

the slug of ticket_type and ticket_type_alias need to be unique

Problem 2

Fetching the ticket_type by slug needs to access both tables. This is not really a problem, but accessing only one table would be simpler.

Current solution

I think I am going to store the primary slug of the ticket-type in the alias table, too.

This solves problem1 and problem2. But on the other hand it is redundant.

Up to now I try to avoid redundancy…..

And I need a way to ensure that this redundancy does not get out of sync.

Better solutions?

Do you have a better solution (or comment)? Please let me know.

DB: PostgreSQL

I use PostgreSQL. But I think this should not matter for this question.

Best Answer

I think I am going to store the primary slug of the ticket-type in the alias table, too.

Yes. And to avoid redundancy, remove it from the table ticket_type and add a prim boolean column to ticket_type_slug_alias, to identify which of the slug aliases is the primary one. In this way, when looking for the ticket_type id by slug you have only to access one table, while when looking for the primary slug knowing the id you’d need to select FROM ticket_type t JOIN ticket_type_slug_alias a on (a.prim = true and a.id = t.id)

Related Question