Indexing varchars & Foreign Keys

Question

I have two tables defined below.

Create table tickets (id long not null,
reseller long not null,
constraint pk_lock primary key (id)); 

Create table ticketRegistrations (id long not null,
customer long not null,
constraint fkTicketRegistrationTicket 
  foreign key (id) references tickets (id) on update cascade);

The client can input tickets (hence no autoincrement for the Primary Key). Since the id is the Primary key AND FOREIGN KEY of the ticketregistrations table there is integrity constraints and all that jazz. The problem I have run into is a feature request which is to allow zero padding with the ticket id (i.e. 00070). Now integers cannot be stored with zero padding to the best of my knowledge.

What solution i have come up with is to add a ticketID varchar(8) not null column in the ticket table and use the actual id for BOTH TABLES as a surrogate key. The foreign key of the ticketregistration table would then point to the ticketid.

The question I have is regarding efficiency and speed. Previously I could add a ticket registration within the system and the database would do an integrity constraint on addition to see if a ticket with the same id is within the database. Now I have a varchar string for an id which will be indexed.
When a customer "registers a ticket" will it be easier to keep the ticketid varchar in the ticket table and use a foreign key of ticketid within the ticketregistration table (also a varchar(8))?

Or will it be easier to NOT have a ticketid varchar(8) within ticketregistrations, keep the foreign key to tickets table as the primary key of the ticketregistrations table and check first for the ticketid within the ticket table, retrieve the value, and input it into a row within ticketregistrations?

This will create an indexed varchar search on the tickets table prior to each insertion into the ticketsregistrations table.

My initial solution did not need this since referential integrity took care of the problem.

I am worried about seek times.

Thank you all for your time!

Snuggs.

Answer 1

I have two solutions.

The first is to have two columns for the identifiers in the ticket table. One should be an internal-only identifier, which would be used for referencing (meaning you can auto-increment it). The key point is that this column would not be tied to any input data. The second column would contain the string representation of the id as entered by the user. You can then add a unique index on the second column for searching purposes.

The second is to keep the tables as they are, but only display the ids with zero-padding to a specified length (i.e., the user can input only 6 digits). So everywhere you need to display an id, call a function that pads it; when a ticket number is input, strip off any padding and convert to an integer. This solution would be easier to implement if you've already got a whole application written around the database structure in your question. It's also more flexible if you think the number of digits will be a variable during the lifetime of the application.

If feasible, you could implement both solutions, adding a second numeric column to the table, and then doing the zero-padding display stuff.

Whichever solution you end up using, think about what you'd have to change if you were asked to add support for ticket identifiers that contains letters as well as numbers.

Answer 2

I would leave the ids and foreign ley constraints as they are. Add a second computed column that is varchar and have it add the leading zeros. Use the first column for all work and display the second column.