[SOLVED] Which datatype would be better?

[Yesideez]

I've got a large database and one particular table lists music events in my area (it's called "events)

 

There is a field in that table called "type" which I'm using to describe what type of event it is like dance, private, trance, techno etc. and the list is quite long, about 15 types.

 

There's another field called "status" which tells me the status of the event, "Disabled" meaning it is withdrawn from public view, "Active" meaning public can view it, "Postponed" meaning it has been postponed, just 3 selections.

 

I also have a third field called "modded" which tells me if a moderator has checked the event. Events can be added by the public so moderators need to check them for content and more. This field contains "Waiting" when an event is newly added, "Safe" when a moderator has OK'd it or "Unsafe" if a moderator has checked it and refuses it for whatever reason.

 

Now so far for the "type" field it's a number which is used to reference another table because a) I'm using a drop-down box when users add events and b) I may need to add new event types in the future. My scripts build the drop-down boxes automatically when adding data and as I add new event types I don't have to change anything else apart from just add another record to the table.

 

Now the real query is with the "status" and "modded" fields.

 

Is it better to use them the same as "types" or as an integer (eg. 1=Waiting, 2=Safe etc.) or as an ENUM?

 

I'm not sure how fast ENUM datatypes are when running a SELECT query. I understand searching for matching integers is faster and more efficient than searching for matching VARCHAR types.

 

Apologies for the size of this post but I really can't get my head around using JOIN in the slightest and when running a SELECT query sometimes I need to search for data matching on various event content, status and modded in different combinations depending on circumstances.

 

Oh, to make matters worse I've also got another table called "users" which contains obvious (haha) information so I also need to pull the username from the users table when listing events as I need to show who added the event as well as the name of the moderator who verified the event.

[btherl]

Have you read through the docs here: http://dev.mysql.com/doc/refman/5.1/en/enum.html

 

You're right that using integers and a join is more flexible.  Changing the enum requires changing the table definition.

 

As for speed, I wouldn't expect much difference.. but as always, you should benchmark it if it's really important.

 

As for joins.. the enum has the advantage of not confusing you as much   On the other hand, joins are important to understand for using databases.  Maybe it's good to learn about them in a simple context like this.  Joining in the way that you are for the types is the simplest kind of join there is.

[fenway]

ENUMs are better in terms of space efficiency, but unless the values will NEVER change, don't bother.

[artacus]

So if you start out with 'A','B','C' a year later you can't add 'D'?

[fenway]

Oh, you can add them, but it's such a headache that the slight improvement you get from a ENUM data type isn't worth it... i'd rather have a CHAR field instead.