help with Database tables

[Ricklord]

Hi,

Im having problems when it comes to adding the tables to by database. I am following a dummies guide book on creating a CMS. I have created the database but the guide isnt very clear when it comes to the information stored in the tables.

This is just a few of the fields i have got for 1 of the tables:

Table name: dept_user

Field names and details seperated by <>

Field:user_name <> Type:VARCHAR(255) <> Primary Key

Field:dept_id <> Type:INT

Field:password <> Type:VARCHAR(255)

There is 7 fields in total but these are the 3 different types for the first table. I am using phpmyadmin on my hosting server to configure the tables and i have the ability to create the table and get to the page where i need to add the fields. I have started adding the fields but become concerend when i was leaving alot of empty boxes for each field.

For example:

Field = name (ok)
Type = VARCHAR (ok)
Lengh/Values = 255 (not to sure on this one but am i right here?)
Collation = (i have no idea what this is for)
Attributes = (i have no idea what this is for)
Null= (null or not null????, i dont know which to choose)
Default= (free text field, again dont know what for)
Extra= (dont know what this is for)

Then at the end of the row i get to choose primary key/index/unique/full text or leave radio button with --- above it selected.

Ok so now i hav etried to explain that as best i can i guess what im after is to find out if the fields i am not sure about are important and if so what value they should be? The main one is Collation as im guessing i need to pick something from the drop down menu here.

Finally on all of the other tables i need to create, they all have a field with type set to { SERIAL } when i look down the type drop down menu i cant see this on there. What should i use instead and would i need to change anything in my php files which link to this or has the SERIAL type been replaced with another type????

I hope i have explained my self so you guys can understand me. Im kinda new to phpmyadmin and mySQL but ive got to start somewhere and as i need a CMS i thought id do it myself from the dummies guide i have.

Thanks for any responses in advance

Rick

[obsidian]

Here are some of my quick thoughts. If you need more clarification, please feel free to ask:

NULL or NOT NULL - this tells the table whether to [b]require[/b] some value to be entered into this field. If you're ever going to have a record without data for this column, you want to allow NULL.

DEFAULT text area - this is where you enter a default value to assign when nothing is provided via your script for this column. So, you only want to put something in that text field if you want to assign a default value to that column.

EXTRA allows you to specify any additional flags or attributes you want to assign to the column. At this point in your learning process, you should probably just ignore it completely.

As for the question about SERIAL, is your tutorial by chance using PostgreSQL, or is it using MySQL? The reason I ask is that SERIAL is the keyword to assign an auto incrementing column in PostgreSQL. In MySQL, the equivalent would be to assign AUTO_INCREMENT to the column. You should find this option under one of your attribute dropdowns.

Hope this helps!

[b]**EDIT**[/b]

Just a quick additional thought: I would highly suggest you learn the SQL required to create the tables you're after as opposed to being overly concerned about learning phpMyAdmin. This will help you in the future much more than learning a specific tool. For instance, a very simple query statement that will create a table such as you have described would be:
[code]
CREATE TABLE dept_user (
  `user_name` VARCHAR(255) PRIMARY KEY,
  `dept_id` INT4,
  `password` VARCHAR(255)
);
[/code]

Just a thought

[Ricklord]

Hi,

Thanx for that reply.

1.let me see if i got a few things straight. NOT NULL would mean something has to be entered in this field and NULL allows the field to be blank.

2.Default text area, i guess would be used for example a login page, so username field come be defaulted to say... <enter your username here>

3.the extra column i will leave blank for now as you suggested

4.as for the reply about using SERIAL as a type. the guide i am using that tells me to use this type is a PHP & MySQL everyday apps for dummies guide. All i can tell you is that ti am using procedural approach instead of object oriented. Is this the best way? Also i have looked down my list for AUTO_INCREMENT type but i cant find it. Any other suggestions.

5. Im still a little worried about the collation column, can i leave this or does it need to be used?


more feedback still needed for clarification on above please.

thanks in advance

rick