Varbinary? Trouble Setting Up The Database In Phpmyadmin

[Tyler]

I'm having a bit of trouble creating the tables for the knowledge is power example site. I'm using phpMyAdmin 3.3.9.2 in MAMP 1.9.6 on a Mac. MySQL version 5.5.9

 

First I tried to copy/paste the code from my digital copy of the book, but I got errors. So I decided to work through adding the tables manually and I got stuck. I'm not an expert on database design.

 

I was doing ok until I got to the users table where it asks for a pass with a type of varbinary(32). I don't seem to have varbinary as an option in my dropdown list.

 

The only differences I can see between what the book says to create and what I've created is the dabase engine. My install of phpMyAdmin defaults to InnoDB instead of MyISAM and I didn't notice this until after I had started creating tables. Do I need to change to MyISAM to see varbinary, or has varbinary been deprecated in my version of phpmyadmin/mysql? If so, what should I use in it's place for the password?

 

Also, you show some fields as "PRIMARY KEY" (which I understand), some as "UNIQUE", and others as "KEY" in the book. KEY isn't an option in my list of dropdowns (I have PRIMARY, UNIQUE, INDEX, and FULL TEXT). I assume you're meaning for these keys to be used as foreign keys. If so, do I use INDEX for them? Again, I'm not an expert on DB design, so thanks in advance for the help!

[Antonio Conte]

Varbinary should be varchar(32). I actually have a Varbinary in my setup. It looks like it is like a number of binaries (zeroes and ones)

 

You don't need to change your databas type. You can do this be clicking the table, selection "Operations", and select a different DB type. There are some benefits and some drawbacks regarding both, so it really doesn't matter else your told to use a spesific type.

 

Unique is kind of like Primary key. It means it cannot exist similar data. You can do a combined unique with several columns. A good suggestion is often an auto_increment Primary key and a combined Unique to make sure data cannot be saved twice. (If you need to make sure of this)

 

Key could be several things. Primary keys, foreign key and possibly some others. A foreign key means a refferance to another tables Primary key. If you ever wanted to use user_id in another table, say sales, the user_id would be a foreign key to the users table.

 

Index and Full Text is used for database optimalization. If a number (lets say user_id, user_name rows is often used in queries, an index would speed up queries when both these coulumns are used. Full Text is used to speed up long columns of text. That is good if you need to perform searches on text. A good example is you need to search in forum post. Full text is made for this.

 

Hope this answeers some of your questions.

[Tyler]

Thanks for your reply. I'd been waiting for an email message, but it never came. I'll have to figure out how to subscribe to my posts.

 

Anyway, I still couldn't get varbinary to show up in my dropdown list, so I took another stab at creating the tables 1x1. I figured out what the error was. In the book there's a new line arrow that shows when a line of text wraps to a new line. When I removed that the error went away.

 

I was able to create all the tables by pasting them into the sql box and pass did get set to varbinary(32). So even tho it wasn't an option, it did get set properly.

 

Thanks!