In MySQL, I need to create a new table called users with the following fields:
id – integer type (primary key)
first_name – varchar type
username – varchar type
password – char type with length 40 (this is the length of a sha1 hash)
type – varchar type (‘admin’ or ‘author’)
Everything looks straight forward except
type – varchar type (‘admin’ or ‘author’) .
How do I do this?
You want to use MySQL's ENUM datatype:
An
ENUMis a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.
Therefore, in your case:
`Type` ENUM('admin', 'author')
you should use ENUM datatype http://dev.mysql.com/doc/refman/5.0/en/enum.html
`type` ENUM ('admin','author')
If you're sure that the user type won't be anything else besides admin or author you can use ENUM:
`type` ENUM('admin', 'author') NOT NULL DEFAULT 'author'
However, this might lead to an anti-pattern if the possible user types needs to expand or when you need to list the different user types in a form. If that's the case you can add another table:
user_types (type)
Where type is a VARCHAR(20) (which should be sufficient length). In your users table you create the same kind of field and add a foreign key to the user_types table.
Btw, don't store simple SHA1() of password, use Bcrypt instead :)
Enum is the collection of list like numbers,days
ENUM('admin','author');
