tags:

views:

58

answers:

6

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?

+6  A: 

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

nilamo
+2  A: 

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.

paxdiablo
+1 for covering nulls.
nilamo
makes sense ill keep that in mind. thanx
I use this technique more and more, and use enums less and less, as I learn (the hard way), that applications need to be flexible.
Fire Crow
A: 

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

Havenard
how would i select the values to be displayed in the selection box? and if a selection of opened is selected through the dropdown menu would it just be a regular save such as "insert into tablename(...) values(.....) ??
A: 

I usually use ENUM in these instances.

PS - NULL is a different value than empty

Ryan
then whats its purpose ?
@iaddesign, NULL specifically means unknown or not-applicable. This is different to "known/applicable but blank".
paxdiablo
+1  A: 

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.

zombat
makes sense, yea i have a problem with understanding how enum works if i have 3 values specified how would i be viewing that data on the client side as well as updating the table to what the user selected in the dropdown.
ENUM is really just stored as an int, but the ints are represented as text. So instead of doing `INSERT INTO tbl VALUES (0)` with a TINYINT column, you'd do `INSERT INTO tbl VALUES ('open')` for the ENUM. When you select it out of the database, you get the 'open' string as well. Internally, MySQL simply stores the possible enum values as ints, which saves on data storage space. The downside is that you can only use the values in the column declaration.
zombat
i think im just going to stick to using tinyint i dont really see a purpose to enums with the application im building. the database wont be bigger than 20 mbs (in the long run) i wonder whats the max size a mysql db can be.. hmm...
A: 

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?

Kirk Broadhurst