Basic MySQL query

Question

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?

Answer 1

You want to use MySQL's ENUM datatype:

An ENUM is 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')

Answer 2

you should use ENUM datatype http://dev.mysql.com/doc/refman/5.0/en/enum.html

`type` ENUM ('admin','author')

Answer 3

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 :)

Answer 4

Enum is the collection of list like numbers,days

ENUM('admin','author');