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,
type ENUM('Admin','User','Guest') DEFAULT 'Guest',
Heres a couple of insert statements
INSERT INTO Members (id,name,email,password,type,created,image) VALUES (1,"Luke Urtnowski","email@example.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","firstname.lastname@example.org","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","email@example.com","test","User",2014-07-07,"http://www.almostsavvy.com/wp-content/uploads/2011/04/profile-photo.jpg");
is this ok?
what happened when you tested it? ™
It seems to work ok, phpmyadmin doesn't complain.
But is it ok to do this?
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
personally, i think ENUM is the spawn of the devil
but if you want to use it, go ahead
Not only that but it makes making changes extremely difficult when you need to add and delete values.
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?
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
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?
when using phpmyadm,in and try to import the tables and rows, i get this error
INSERT INTO Members (
image) VALUES (1,"Luke Urtnowski","firstname.lastname@example.org","test",0,2014-07-07,"http://www.almostsavvy.com/wp-content/uploads/2011/04/profile-photo.jpg");
1452 - Cannot add or update a child row: a foreign key constraint fails (
members_ibfk_1 FOREIGN KEY (
what does that mean?
it means there is no row in the types table with a PK of 0
btw, your date value needs quotes around it
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?
Yes. All that means is that the table is related to more than one other table.
it's actually the other way around... the FK links to the PK
This topic is now closed. New replies are no longer allowed.