Using enums

Databases
1

I am trying to figure out if m using ENUMS correctly, I have a table that can hold up to three types of members
Heres the new table

CREATE TABLE Members(
id SMALLINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(50),
email VARCHAR(50),
password VARCHAR(50),
type ENUM('Admin','User','Guest') DEFAULT 'Guest',
created DATE,
image VARCHAR(150)
);

Heres a couple of insert statements


INSERT INTO Members (id,name,email,password,type,created,image) VALUES (1,"Luke Urtnowski","lurtnowski@gmail.com","test","Admin",2014-07-07,"http://www.almostsavvy.com/wp-content/uploads/2011/04/profile-photo.jpg");
INSERT INTO Members (id,name,email,password,type,created,image) VALUES (2,"Jason Cannon","jason@gmail.com","test","User",2014-07-07,"http://www.almostsavvy.com/wp-content/uploads/2011/04/profile-photo.jpg");
INSERT INTO Members (id,name,email,password,type,created,image) VALUES (3,"Davida Milkes","jason@gmail.com","test","User",2014-07-07,"http://www.almostsavvy.com/wp-content/uploads/2011/04/profile-photo.jpg");

is this ok?

2

what happened when you tested it? ™

3

It seems to work ok, phpmyadmin doesn’t complain.

But is it ok to do this?

4

If the value isn’t one of the ENUM the INSERT will fail.
You’ve taken this into account?

Can you think of a better datatype than ENUM for
‘Admin’, ‘User’, ‘Guest’ DEFAULT ‘Guest’ ?
eg. Int Null, 0, 1

5

personally, i think ENUM is the spawn of the devil

but if you want to use it, go ahead

6

Not only that but it makes making changes extremely difficult when you need to add and delete values.

7

Jeez, I heard enums were good, I had the type TINYINT where the numbers id use are 0 and 1 (I may add more later). Where 0 is the Admin and 1 is a registered user (to be converted using a PHP case statement to be converted later.Is that better?

8

not quite

don’t convert it with php, because that creates a potential disconnect between the database values and the php code

instead, create a separate table for member types

TINYINT for the PK, VARCHAR for the description

then in your members table, make sure to declare the member type as a FK

9

ok, I have three tables (Properties, Members, and Types)
The PK of the Types table is a FK in the Members Table and the PK of the Members table is a FK in the Properties table. It that a good set up?

10

when using phpmyadm,in and try to import the tables and rows, i get this error

[h=1]Error[/h]SQL query:

INSERT INTO Members (id,name,email,password,type_id,created,image) VALUES (1,“Luke Urtnowski","lurtnowski@gmail.com”,“test”,0,2014-07-07,“http://www.almostsavvy.com/wp-content/uploads/2011/04/profile-photo.jpg”);
MySQL said:
#1452 - Cannot add or update a child row: a foreign key constraint fails (shoresrentals.members, CONSTRAINT members_ibfk_1 FOREIGN KEY (type_id) REFERENCES types (id))

what does that mean?

11

it means there is no row in the types table with a PK of 0

btw, your date value needs quotes around it

12

thanks

13

I have a question, in one of my Tables, I have building which is numbered 1-10, then I convert thatr number into the bulding name with PHP later. I’m thinking it would be better to make a Buildings table and have its PK link to a FK in the Properties table, but is that fine to have a table with two FKs?

14

Yes. All that means is that the table is related to more than one other table.

15

it’s actually the other way around… the FK links to the PK

16

ohhhh
thanks