String or Enum

Question

Which situation is better?

Assuming upwards of 300 possible values on what would essentially be a table of key/value pairs where the column in question is the key:

Enum or Char/Varchar

I'm just wondering if anyone has dealt with an Enum column with that many possible values and what the pitfalls might be.

EDIT:

Keys ARE predefined.

Item Table:

id, name, ...

Attributes Table:

id, item_id, key, value

Answer 1

If you are talking 300 possible values, I would be using a separate id/value lookup table.

Especially given this statement from your question:

on what would essentially be a table of key/value pairs where the column in question is the key

So why not just actually make it a table of key/value pairs?

Answer 2

Perhaps I misunderstood the question, but normally if I have a column with 300+ types that tells me I need to normalize it and put it in another table, using a foreign key.

Answer 3

I suggest to choose an enum if there are only orthogonal values - for example named colors are an example that could justify creating a large enum. The question is if your 300+ values are orthogonal - can you decompose it into smaller orthogonal sets? For example the eight corners of a cube could be described by an enum with eight values.

FrontLeftBottom
FrontRightBottom
FrontRightTop
FrontLeftTop
RearLeftBottom
[...]

But you could also decompose it into only six values and combine them using a bitflag enum.

Front
Rear
Left
Right
Top
Bottom

If the value space is not fixed - that is it is quite possible that new values will be added - a string or a new deticated type is the way to go.