[SOLVED] Use index or replace values by surrogate ids and add a separate table?

Use index or replace values by surrogate ids and add a separate table?

Currently I've got a couple of pivot tables with this structure in mysql:

user_id | organisation_id | type

4   3   external
4   3   internal
6   4   internal
7   4   external
6   5   external
7   5   external
7   6   internal

As you can see, in my type column I only use external and internal. Currently the type column is a enum and is indexed.

Is this a good database structure or should I make a separate table for the column type?

I've got this "problem" with a couple of tables so it's an important decision. The table could become quite big.

What would you do in this situation?

Solution

This depends a great deal on the kind of queries that you are going to run against this table.

If you plan to run queries that ignore the type column, i.e. do not care if it's internal or external, or queries that decide what type they want at run-time, e.g. through user input, there is a great advantage to keeping everything in a single table.

Generally, indexing a column with poor selectivity, i.e. with roughly half the rows in each of its two categories, can deliver only marginal performance improvements compared to a full table scan. Same goes for splitting the table in two: it will make queries more complex with very little improvement.