Help on creating database with multiple tables

[dipp02]

Hello i need a little advice and help.

 

I am coding a resume builder website. Right now i have the login system coded with different access levels. and i am starting to create the actual resume builder itself and need some help on creating the wizard database.

 

For the login system there are 4 tables. (users, active_users, active_guests, banned_users)

In the users, active_users, banned_users table there is a primary key for the field username.

 

Here is the SQL code for the login system:

#
#  Table structure for users table
#
DROP TABLE IF EXISTS users;

CREATE TABLE users (
username varchar(30) primary key,
password varchar(32),
userid varchar(32),
userlevel tinyint(1) unsigned not null,
email varchar(50),
timestamp int(11) unsigned not null
);


#
#  Table structure for active users table
#
DROP TABLE IF EXISTS active_users;

CREATE TABLE active_users (
username varchar(30) primary key,
timestamp int(11) unsigned not null
);


#
#  Table structure for active guests table
#
DROP TABLE IF EXISTS active_guests;

CREATE TABLE active_guests (
ip varchar(15) primary key,
timestamp int(11) unsigned not null
);


#
#  Table structure for banned users table
#
DROP TABLE IF EXISTS banned_users;

CREATE TABLE banned_users (
username varchar(30) primary key,
timestamp int(11) unsigned not null
);

 

 

Now once the user logins they will be able to view the page wizard.php and be able to start the resume builder wizard. Once the user is in the wizard.php i want the contact info form to show up and once the next button is hit the profile form shows up and so on... (this i have down pretty good).

 

After the resume info is collected the info will be put into wither a .doc or .pdf into a resume template format. (may need help when i get to this part)

 

I just want to make sure i am headed towards the right direction in creating the database for the wizard. So far, I have created 4 tables for the resume builder - (wizard_contact, wizard_profile, wizard_edu, wizard_custom)

 

I made separate tables because each table will be associated with a different form. (ex. contact info form will store into wizard_contact, profile form will store into wizard_profile, etc..)

 

 

here is the code i have down for the SQL database for the wizard so far-

/*
#  Resume Builder Database
#  wizard_contact
#  wizard_profile
#  wizard_edu
#  wizard_custom
#
*/
#  
#  Table structure for wizard contact table
#
DROP TABLE IF EXISTS wizard_contact;

CREATE TABLE wizard_contact (
resumeid mediumint(9) primary key not null auto_increment, 
username varchar(30) foreign key,
fname varchar(30) not null,
lname varchar(30) not null,
street varchar(80) not null,
city varchar(30) not null,
state char(2) not null,
zip mediumint(5) unsigned not null,
phone varchar(14) unsigned not null,
cell varchar(14) null
);


#
#  Table structure for wizard profile table
#
DROP TABLE IF EXISTS wizard_profile;

CREATE TABLE wizard_profile (
resumeid mediumint(9) foreign key,
profile text not null,
keystrength1 varchar(30) not null,
keystrength2 varchar(80) not null,
keystrength3 varchar(30) not null,
keystrength4 varchar(30) not null,
);

 

 

I stopped there because i just want to make sure i am setting up the database correctly. Here is where i need some advice/help.

 

there is a resumeid that is going to be the primary key and an automatic number for that certain resume. i did this so the user can create more than one resume. (am i right here?)

 

Also i brought in the username as a foreign key because i dont want everyone to be able to view each others resumes. and also to make sure that certain resumeid is assocated with that username. (am i right here?)

 

for the wizard_profile i brought in the resumeid as the foreign key so it can be associated with the correct info in wizard_contact. (am i right here?)

 

now this is what i would do for the next table wizard_edu...

 

#
#  Table structure for wizard edu table
#
DROP TABLE IF EXISTS wizard_edu;

CREATE TABLE wizard_edu (
resumeid mediumint(9) foreign key,
school varchar(30) not null,
startdate date not null,
enddate date not null,
degree varchar(30) not null,
major varchar(30) not null,
minor varchar(30) unsigned not null,
educity varchar(30) unsigned not null,
edustate char(2) null
);

 

 

Was i clear on what i am trying to do? Please let me know if i am doing this correctly. or if i am semi correct. or if i am just totally wrong.

 

i will take all the advice i can get.

 

Thanks in advance.

 

 

[dipp02]

Any Advice? am i setting these tables up correctly?

 

Thanks

[luca200]

My two cents:

you are not on the right way.

I think you never heard anything about normalization. Your tables are not normalized.

You should not have more than 1 table with the same primary key. Instead, you have 3 users table with the same key, and 3 resume tables with the same key. That's not a good thing, imho.

I can't say more than this because I'm not sure I understood what you're trying to do.

 

[dipp02]

My two cents:

you are not on the right way.

I think you never heard anything about normalization. Your tables are not normalized.

You should not have more than 1 table with the same primary key. Instead, you have 3 users table with the same key, and 3 resume tables with the same key. That's not a good thing, imho.

I can't say more than this because I'm not sure I understood what you're trying to do.

 

 

OK thanks i just needed to know if i was doing it right before i start actually doing it.

 

I will look up normalization and post back.

 

Thanks for your input.

 

[dipp02]

Intro

Ok i spent pretty much all day reading up on MySQL and database realtionships and normalzation.

 

I even went out and bought two books to read up on.

1. How to do everything with PHP and MySQL- Vikram Vaswani

2. Beginning PHP and MySQL E-Commerce from novice to professional 2nd edition - Danrie and Balanescu

 

 

Ok so since my first post is totally wrong i would like to start fresh and give as much detail as possible.

 

First off, Thise is the current site my client has up now. www resumeref.com - You can see for yourself to see kinda what i am trying to do. login with my test account and see how the resume builder works. but my goal is to make it look alot cleaner, and flow nicer.

Login info: UN:bob@dbhosts.net PW:qwertyui

Also i just i just put that current header up as a temp. because the client is changeing the site over to downloadmyresume domain name since he doesnt like the ref concept.

 

But anyways the site is in ASP and i do not know much of ASP so i am recoding the whole site from top to bottom. i made a new design and everything in PHP MySQL and CSS. here is my current test site for the new design www dbhosts.net/resume/login/

 

What i am trying to do:

I am creating a resume builder wizard but the user must be signed up to start using it. once the user has a login the wizard will go through about 5-6 steps to create the full resume. After the user goes through all the forms and has previewed the resume they will be prompt to select a membership type and then pay through paypal. Once the payment is sent the wizard is going to e-mail the user their resume in either .doc format or .pdf format and also host thier resume on the downloadmyresume server for 90-days.

(Any Suggestions with how i will convert the data from mysql into a .doc template or pdf, then host it for 90days?),  

 

There will be different user levels to determine the users membership. There will be four kinds of memberships:

Regular User - just signed up for a login. never bought a resume.

Subscribed - for around 12 bucks- you get 1 executive resume emailed to you and it will be hosted for 90days. 

Lifetime - for around 50 bucks - the user will be able to come back edit, add new resumes at anytime and every resume will be hoste for 90days.

Admin - has control over the Admin center which will be able to view the clients and memberships etc.

 

Right now i am creating the database. and this is what i came up with so far. one table for the users and then i am going to have 6 tables for the wizard. one called wizard that holds the reusmeid username userid and userlevel. the others will be called wizard_contact, wizard_profile, wizard_edu, wizard_exp, wizard_custom. each table will be a different step for the user to enter info into on the site.

 

( Please, any advice, suggestions with the following? it will be very helpful)

note: i only posted the first three tables of the wizard and the users table. i need to know if i am doing this right.

#
#  Table structure for users table
#
DROP TABLE IF EXISTS users;

CREATE TABLE users (
userid int(11) NOT NULL auto_increment,
username varchar(30),
password varchar(32),
userlevel tinyint(1) unsigned not null,
email varchar(50),
timestamp int(11) unsigned not null,
Primary Key (userid, username)
type=INNODB 
);


/*
#  
#  Table structure for wizard table
#
*/
DROP TABLE IF EXISTS wizard;

CREATE TABLE wizard (
resumeid int(11) not null auto_increment,
users_userid int(11) unsigned not null,
username varchar(30),
userlevel tinyint(1) unsigned not null,
timestamp int(11) unsigned not null,
Primary Key (resumeid),
Foreign Key (users_userid) references users(userid),
type=INNODB 
);

/*
#
#  Table structure for wizard contact table
#
*/
DROP TABLE IF EXISTS wizard_contact;

CREATE TABLE wizard_contact (
contact_resumeid int(11) not null,
fname varchar(30) not null,
lname varchar(30) not null,
street varchar(80) not null,
city varchar(30) not null,
state char(2) not null,
zip mediumint(5) unsigned not null,
phone varchar(14) unsigned not null,
cell varchar(14) null,
Foreign Key (contact_resumeid) references wizard(resumeid),
type=INNODB 
);


#
#  Table structure for wizard profile table
#
DROP TABLE IF EXISTS wizard_profile;

CREATE TABLE wizard_profile (
profile_resumeid int(11) not null,
profile text not null,
keystrength1 varchar(30) not null,
keystrength2 varchar(30) not null,
keystrength3 varchar(30) not null,
keystrength4 varchar(30) not null,
Foreign Key (profile_resumeid) references wizard_contact(contact_resumeid),
type=INNODB 
);

 

Am i headed towards the right track now with the database? was i clear enough in what i need to do?

 

please let me know, anything will be helpful.

 

Thanks in advance.

[dipp02]

can anyone help me?

 

am i setting up the databases correctly in post #5 ?