MySQL updating 'categories' linking table

Question

I have a table that holds bibliography entries, with a bibID primary key. I also have a table that holds a list of categories that can be assigned to the bibliography entries with a categoryID primary key. A table links these two tables as bibID:categoryID, so that each bibID can be associated with multiple categoryIDs.

Categories associated with bibliography entries can be edited via a form with checkboxes that represent all possible categories.

What is the most efficient way to update this relationship? I could just delete all relationships from the linking table associated with an entry and then reinsert whatever the form says, but this seems inefficient.

Answer 1

Efficiency is a slippery term. It can mean different things to different people.

However in most cases it means "performance", so I will assume that is what you mean for now.

I suspect the reality is that this is the most efficient (performant) way.

Other methods may appear more elegant, as they will preserve existing data, and only add missing data, but they will (potentially) require more database accesses and (definitely) more complicated SQL. One database call to delete and one to add should fix you up.

The only exception may be where there are large numbers of entries and the changes are small (or negligible). In this case you may need to reconsider.