ENUM fields

[kyfamilies]

I'm pretty new to MySQL and have been scouring the internet looking for all information and opinions on using ENUM fields. I'm a volunteer project coordinator for a VERY large project which will contain millions of records so I want to set it up correctly.

The database contains birth, marriage and death information.

Questions:
1. I've seen several opinions that it is preferable to use enums whenever possible as increasing the number of tables will slow down the database. True or false?

2. Is it a bad idea to use enum fields for numeric values (we are currently looking at days of the month)? Would it be very confusing to have numbers 01-31 in the enum field?

3. The MySQL manual states the storage requirement for an enum field is 1 byte up to 255 members. Does this mean the table stores the member number instead of the member name? In other words, if I have a enum field that has the names of 120 counties in a state, will the table store the county names or the member number?

4. If I use forms to gather the information, should I use the member name or the member number for the output for an enum field?

Thanks so much for your tolerance of yet another newbie!

 

[TonyGroves]

You shouldn't get too hung up on saving space; just use whatever database structure and field types that are natural for the data. It'll make things a lot simpler.

I certainly wouldn't use an enum for days of the month - that would easily fit into a TINYINT field, or better, use a DATE field for the entire date.

Regarding county names, you could use an ENUM here, and it would probably be better than storing the entire name, but if there are spelling variations, you're in trouble. What would most likely be better would be to have a separate table of counties with a code (there is probably a standard set of county codes available) for each, and reference each county by its code.

 

[kyfamilies]

Still one question:

Does the ENUM field really only take 1 byte of storage -- even if my enum members are 12-letter words?

 

[TonyGroves]

An ENUM field of up to 255 entries will occupy one byte. If there are between 256 and 65535 entries, two bytes will be used.

However, think carefully before getting too involved with ENUMs. They have their uses, but also limitations.