0

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.

1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.