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
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?
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.
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.