Lookup table for a one-to-many relationship

I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.

tickets
- id
- sutff

lookup
- outside_data_id
- ticket_id

I just need to know if the association exists (hence the lookup table) and has a one to many relationship. I know nothing else about the outside_data_id except the id itself and the related ticket_id.

Also I don't want to alter the tickets table as this will only affect a small amount of ticket_id's.
Example

outside_data_id = 1234

Can be related to:

ticket_id = 321
ticket_id = 322
ticket_id = 900

Would the below lookup table structure work or is there a better way?

CREATE TABLE lookup
(
   outside_data_id integer, 
   ticket_id integer
)

Your table will work fine for this purpose, but you probably want to add an index. If the primary reason for using this table is to take an outside_ticket_id and get the corresponding ticket_id's I would add the following clustered index:

CREATE CLUSTERED INDEX [CL_Lookup_OD_ID] on [lookup](outside_data_id)
GO

If the primary lookup will be the other way around (trying to find the outside_data_id from a ticket_id) place the clustered index on the other column.

---- Oh, sorry, just noticed this is Postgres. The above syntax is SQL Server. For Postgres, create an index on the column, then issue the cluster command, like so:

CREATE INDEX IX_outside_data_id on lookup(outside_data_id);
CLUSTER lookup using IX_outside_data_id;

You may also want to look at the "fillfactor" on the index depending on how heavy the insert load is here. But that is a large topic worth exploring on it's own...

StackExchange Database Administrators Q#25291, answer score: 5