if a mysql databases table is going to hold two to four values should i use a varchar?

Question

i am creating a database structure for a new project and there is a table which one of the fields will receive/contain one of three values upon selection from a selection box on the website. the values would be (opened,suspended,closed) would it be necessary to make the field of type varchar(some value) or is there a better way of doing it? also some other times ill have a field who will accept one of two values which are a yes or a no and i usually just set the field of type varchar(3). i want to do it the correct way what other types can i set the field for these types of instances?

thanks

ps:

whats the purpose of setting a field to not null when creating the columns in a table? all that does is show an error when the developer doesnt insert a value for a field that is required. all that checking can be done on the client side or server side, whats its purpose of being done through the database? is it just there to make developers carefull for what fields are required for data entry?

Answer 1

Check out the enum type. It lets you have a set number of options, and is normally more efficient because of it.

Answer 2

Ideally, I would actually have an integer field there which was a foreign key to another table, something like:

MyTable
    Field1 varchar(10)
    State integer

States
    State integer primary key
    Desc varchar(50)

This will alleviate any concern you may have with using up too much space in your DB. Another solution is to use hard-coded integers to specify the state but this introduces unnecessary tying between your DB and your application.

As to your question as to using "not null", the DBMS should be solely responsible for its own data integrity. Who's to say that application A relies on the fact that a column cannot contain NULLs and application B comes along and inserts them (bug, incompetent coder or any other reason)?

That would potentially break application A. The right place for controlling what data is in the DBMS is at the DBMS.

Answer 3

ENUM('opened', 'suspended', 'closed')

Answer 4

I usually use ENUM in these instances.

PS - NULL is a different value than empty

Answer 5

TINYINT unsigned is usually a better field type for this, although ENUM is a possibility as well. I tend to avoid ENUM because it's a bit of a hassle to roll out a new value in your code if have to put in database changes as well.

A TINYINT only takes a single byte of storage, and can represent up to 256 different values, as opposed to storing a varchar of 'suspended', which takes up 9 bytes, plus one prefix byte. In this case, I would use 0, 1 and 2 to represent 'open', 'suspended' and 'closed' respectively. Again, ENUM allows you to use the text representation rather than the integer, but requires a column change if you want to add a new value.

As for your questions about NOT NULL, yes, that is a method utilized to help ensure data integrity. It is surprisingly useful if you have the foresight to make it part of your table structures.

Answer 6

also some other times ill have a field who will accept one of two values which are a yes or a no and i usually just set the field of type varchar(3). i want to do it the correct way what other types can i set the field for these types of instances?

You should use a boolean / bit type field, which only accepts two values: true and false. You name the field so that true/false values make sense, e.g. isValid, isActive, isElectronic.

Your other three-valued field should similarly have an enum or a foreign key to another table.

The purpose of this is to ensure that you know valid values that might be contained in the database. Strings are not good because there is too much variance. For example, another programmer might write some code that spells the value differently. What would happen then?