String or Enum

Go To StackoverFlow.com

2

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

2009-06-16 18:49
by Tom
enums would be a bad idea for this scenario, immediate code smell, maintenance nightmar - BlackTigerX 2009-06-17 17:09


3

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?

2009-06-16 18:52
by Eric Petroelje
I just edited my question - Tom 2009-06-16 19:01
Honestly, I don't think it would change my answer. I would still have the "key" in your attributes table be an FK a 3rd table with the 300 possible "key" values in it - Eric Petroelje 2009-06-16 19:08


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.

2009-06-16 18:55
by Chet


1

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.

2009-06-16 18:59
by Daniel Brückner
Ads