Enum data type versus using new table

sprogo

What are pros and cons using enum data type instead of using another data type and putting the values in another table?

Thanks guys for your thought on this matter.

sprogo

devinemke

if you are reasonably sure that the elements of the ENUM will be few and not change often then use it.

sprogo

Thanks Devinemke,

I am pretty sure that the elements of ENUM will not change but I do not have any idea if 7 or 8 elements can be assumed as few.

What is the reason you suggest me to use ENUM type? Speed, size, clear coding or something else ? I'd be glad if you clarify it a little bit more.

devinemke wrote:
if you are reasonably sure that the elements of the ENUM will be few and not change often then use it.

Sxooter

Here's a few more points to consider:

enum type is relatively non-portable. You could probably use an array in postgresql, and some other databases have such structures.

enum type allows integers to be inserted, which act as an index to the enum type. I.e. you define an enum with types 'red','blue','yellow' and insert the number 1, you will be storing one of those colors. Not a big deal, more something to keep an eye out for.

enum types are less obviously pulled from the dataset. You have to use a desc tablename to get the output and parse it to get the actual options. So, most people just hard code the options into their apps. This can make upgrading / changing an application a nightmare, so at the least, abstract out the enum entries into an include file so there's only one place that has them. OTOH, if they are entries in an FK table, then all you do is update the table and cello, you've got a new entry in the app on the fly.

Remember that enum was pretty much invented because back in the day, MySQL didn't have FK->PK relationships, and this was a workaround. Now that reason for being has pretty much disappeared.

There are some other reasons I can't recall right now for not using them, but it's not a critical issue. I would recommend that if you're not comfy with relational theory, then you should get so before making up your mind on this or any other decision about db design.

sprogo

Hi Sxooter,

I am actually a bit confused after these posts.. As far as I understand from your post for the protability and easy coding reason, you don't recommend me using enum data type.

This will be a part of an auction script and I am trying to implement an escrow system with a few additions to codes and database. (Pls. look at this thread : [URL]httphttp://www.phpbuilder.com/board/showthread.php?t=10313662[/URL] )

2 says use enum and 2 says not use it. Am I right to be confused?

Sxooter

OK, the first rule of perfomance optimization is: Don't

I.e. don't worry about which is faster for now, worry about which works better (i.e. more correctly) is less error prone, easier to maintain, port, etc...

For 99% of all applications, the difference these two points will make is too small to worry about.

So, pick which one you think is the better choice for you. For me, enum isn't a choice, because I never write mysql only applications. Portability is too important to me to use mysqlisms much.

But for you, who knows? Only you.

I would bet that speed wise, it's a wash. A table with 7 entries is so small as to never get flushed out of memory, and even the dumbest of query planners can figure out how to join it efficiently to another table.

csn

Do you use Postgresql arrays for anything, Sxooter? I haven't come across any data yet that I wouldn't rather just put in a separate table. Though if I was doing a GIS type app, maybe I'd store something like waypoint coordinates in an array.

Sxooter

Not generally. If I need to store an array, I just serialize a php array and shove that into the database. I don't generally use pgsql's arrays or mysql's enums ever. And performacne wise, the diff is pretty small.

I mean, I worry about performance from the gross picture, trying not to do anything too terribly stupid performance wise, but this kinda of stuff is too small to notice compared to the big things one can do wrong.