Very often there is a task to store some type data:

Колонка пол: мужской, женский. Колонка ваше образование: Экономист, Психолог, Юрист. Колонка категории товаров: Стулья, Столы, Шкафы, Кровати, Диваны.

Convenience from the source code is not considered and does not interest. The question is purely database organization, as well as storage of this type of data.

UPD.

The question is not how to organize data storage besides enum or integer (what you started to offer directories, or a boolean value). The question is how to store such data that falls under the enum definition in enum or integer (I repeat, not in boolean, not in reference books, or in any other way, but in one of those that I have listed). Maybe I'm not quite correct examples led, which confused you.

Here are other examples:

 1. Какие магазины вы посещаете чаще всего: Супермаркет, Мегамаркет, Гипермаркет, обычный магазин, киоск, рынок. 2. Какой у вас рост: низкий, средний, высокий, огромный 3. Какой у вас размер одежды: M, S, L, XL, XXL, XXXL

In the examples above, I do not see the point in making reference books.

    2 answers 2

    Column gender: male, female.

    In general, flags (columns with да/нет values) are, of course, more convenient to store in boolean (1/0).
    But specifically the column "gender" I would have stored in char(1) . Especially if the field is required. The system resources char(1) will not eat much more than a boolean , but purely organizationally for any kind of reports is easier: М/Ж displayed, not 1/0 , which are then somehow translated into the same М/Ж still need ...
    Once again: this is only specifically for the column "floor".

    Column your education: Economist, Psychologist, Lawyer.
    Column product categories: Chairs, Tables, Wardrobes, Beds, Sofas.

    Here I agree with the option number 1 from @Suvitruf: use directories. Separate reference tables with variants of values, and in the main table to store only id from the corresponding reference book.
    There are many advantages:

    1. editing: if the value changes, you change it only in one place
    2. organization: if the order of these values ​​between themselves is important, then it is possible (necessary) to store the sequence number in the queue in this directory
    3. organization (2): if there are any attributes inherent in each value - you can also store it in the same reference table without touching the main one
    4. multilingual: if your project is in several languages, then you keep all the options in the directory, and adding another language will not be a problem for you

    Still.
    If you have several fields for which reference tables can be used, and the options in these directories will be only text without any attributes (see paragraphs 1-3 of the list above), then you can organize one table- reference to several columns.

    Those. A table of this type is created:

     id [int] PK attribute [enum(education, category)] value varchar(32)

    It contains all the data:

     id| attribute | value ---------------------- 1 | education | Экономист 2 | education | Психолог 3 | education | Юрист 4 | category | Стулья 5 | category | Столы 6 | category | Шкафы 7 | category | Кровати 8 | category | Диваны

    And already id this one table are brought in the main.
    Strictly speaking, even here you can organize the sequence (point 2 of the list above), but these are details.

    UPDATE

    1. What stores do you visit most often: Supermarket, Megamarket, Hypermarket, regular store, kiosk, market.
    2. How tall are you: low, medium, high, huge
    3. What is your clothing size: M, S, L, XL, XXL, XXXL

    In the examples above, I do not see the point in making reference books.

    Well, if you have a fixed set of values, and this set will not change, then I think it is easier to store in enum .
    Moreover, enum takes 1 or 2 bytes (depending on the number of values), and int - as much as 4 bytes :)

    In general, my answer: enum

    • update the question (15 bukaf) - vladiik
    • Thank! It turns on one enum value from 1 or 2 bytes, and if we say 6 values, it will take up 10 bytes to occupy space. It turns out the enum is no longer profitable, since the int takes 4 bytes? - vladiik
    • @vladik, no, no, you did not understand ... int then it will not be 4 bytes, but 24 :) Apparently you did not quite understand - MySQL stores the enum field as a reference table :) Only this is at the system level of some hidden tables . Do you understand? Here is a dock explaining how much each type of field takes up: dev.mysql.com/doc/refman/5.1/en/storage-requirements.html - cyadvert

    Gender - you can bool 'om. In MySQL for this BOOL / BOOLEAN , the same TINYINT(1) .

    About the same two other cases. Education and goods, in fact, enum 's. But I usually do not use them. I see two solutions in addition to enums:

    1. It seems the most logical option is to use directories. Say, in the case of education, you will have an Education table-reference book, in which the entries are: Economist, Psychologist, Lawyer, etc. Then in the table, where you need education in the column, you refer the secondary key to the record from the first table. With this option, you can easily add new types of education (or goods, if we are talking about them), etc.
    2. Store your enum in the database:

    If you have

     enum Education{ Экономист, Психолог, Юрист }

    a) In the form of int'ov. Then while saving in the database (int)Education.Экономист

    b) In the form of strings.

    When saving to the Education.Экономист.toString() database. Education.Экономист.toString()

    In the form of ints it is easier, less space, in general, buns are obvious. Problems begin when you change the enum client (insert something in the middle, a new product is added), then the order of the elements changes. And everything will be covered with a copper basin. So, in this version, I would recommend in the form of strings.

    • Option 1 is clear. And what about option 2. "store enum in the database" - do you mean so and store with text? Those. have a varchar field where and enter "The Economist", "Psychologist", "Cabinet", "Chair". - cyadvert
    • Updated the answer) - Suvitruf ♦
    • @cyadvert yes, like varchar. But, still, I recommend better add. table reference make - Suvitruf ♦
    • @Suvitruf Updated his question - vladiik
    • @Suvitruf agree - cyadvert