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.
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 );
the slug of ticket_type and ticket_type_alias need to be unique
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.
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.
Do you have a better solution (or comment)? Please let me know.
I use PostgreSQL. But I think this should not matter for this question.