[SOLVED] database inplementation does it look correct please cheers.

[redarrow]

 

advance thank you

 

before i dive into programming my next project does the database inplementation make sence cheers.

 

A small databse for a shop does it make sence please cheers.

<?php

/*

database name                   DATABSE FIELD TYPES
shop                           *********************
****

users
******
account_id                  ** SMALL INT 5 NOT NULL ZERO AUTO_INCREMENT PRIMARY_KEY
account_name                ** VARCHAR 25 NOT NULL**
account_email               ** VARCHAR 40 NOT NULL**
account_surname             ** VARCHAR 40 NOT NULL**
account_address             ** VARCHAR 200  NOT_NULL**
account_post_code           ** VARCHAR 10 NOT NULL**
account_phone_number        ** INT  NOT NULL**
accoubt_activation          ** INT  NOT NULL DEFULT 0 **
account_join_date           ** INT NOT NULL**
account_last_login          ** INT NOT NULL**
account_ip                  ** INT NOT NULL**

products
*********
account_id                 ** SMALL INT 5 NOT NULL ZERO **
product_id                 ** INT NOT NULL AUTO INCREMENT **
account_name               ** VARCHAR 25 NOT NULL **
account_email              ** VARCHAR 40 NOT NULL **
product_item               ** VARCHAR 200 NOT NULL **
product_pic                ** VARCHAR 40 NOT NULL **
product_quinty             ** INT NOT NULL DEFULT 0 **
product_price              ** INT NOT NULL **
purcase_date               ** INT NOT NULL **
note                       ** MEDUIM TEXT NOT NULL **


products_pre_order
******************
account_id                ** SMALL INT 5 NOT NULL ZERO**
account_name              ** VARCHAR 25 NOT NULL **
account_email             ** VARCHAR 40 NOT NULL **
product_id                ** INT NOT NULL **
product_item              ** VARCHAR 200 NOT NULL **
product_price             ** INT NOT NULL **
note                      ** MEDIUM TEXT NOT NULL **

products_free
*************
account_id               ** SMALL INT 5 NOT NULL ZERO **             
account_email            ** VARCHAR 40 NOT NULL **
product_count            ** INT NOT NULL **
product_match            ** INT NOT NULL DEFULT 10 **


products_temp
************
temp_id                   ** SMALL INT 5 NOT NULL ZERO AUTO_INCREMENTB **
product_id                ** INT NOT NULL **
product_item              ** VARCHAR 200 NOT NULL **
product_price             ** INT NOT NULL **
purcase_date              ** INT NOT NULL **


*/

?>

[soycharliente]

My $0.02 (I'm not a DB guru, just my opinion from what I've learned so far):

 

Some of your INT fields don't have lengths.

 

I would use WAY more than 40 for an e-mail address field. Depending on the domain, it could reach 30 just with the @domain.tld [i use 100]

 

Depending on how big this db will be, if any of those fields will ALWAYS have a certain number of characters [ex: in my db, user passwords are passed in with an md5 hash so all of them are 32 characters, so i use CHAR(32) instead of VARCHAR(32)] you might want to not let string length be variable. Might save space/time. Someone also once told me to use a power of 2 for length if possible because of how data is stored. Can someone comment on that?

 

Looks like a lot of data is repeated in multiple tables. Create an associative table.

[redarrow]

ok email noted and int size.

 

 

Create an associative table <<< what do you mean please i use joins

[soycharliente]

You have an account_email field in EVERY table. Are those fields going to be the same in all tables?

[redarrow]

yes m8

[soycharliente]

Ok. So that means you'll have to update it like 5 times if it ever changes. What if they type their address in wrong? That's way too much stuff to update. You'll never be able to remember every single place their email is listed.

 

So you create a table that associates their email with the other tables.

Kind of like this:

account_email

table1_id

table2_id

table3_id

 

You you have the email, then an id for say the user table, the product table, etc.

[redarrow]

very intresting have you got a tutoral on ths please not seen or done associative table

will defently fast things up on the project at hand used google had no reel luck.

[redarrow]

associative table with mysql.

 

i understand that i dont really need all those email there in the database but you mention associative table with mysql or a database what your saying that you can set a entry to be the same entry on all mathching fields.

 

now i got all my books out and on a mission how associative table work.

 

 

i can not find any information on associative table in mysql how the hell do you do it i need to no lol.

 

please someone post a proper tutoral or a link going mad here lol.

[aim25]

Maybe you should make the account_name field a unique_key.

[redarrow]

solved end result.

 

 

<?php

/*

database name                   DATABSE FIELD TYPES
shop                           *********************
****

users
******
account_id                  ** SMALL INT 5 NOT NULL ZERO AUTO_INCREMENT PRIMARY_KEY
account_name                ** VARCHAR 25 NOT NULL**
account_email               ** VARCHAR 100 NOT NULL**
account_surname             ** VARCHAR 40 NOT NULL**
account_address             ** VARCHAR 200  NOT_NULL**
account_post_code           ** VARCHAR 10 NOT NULL**
account_phone_number        ** INT  20 NOT NULL**
accoubt_activation          ** INT  3 NOT NULL DEFULT 0 **
account_join_date           ** INT 20 NOT NULL**
account_last_login          ** INT 20 NOT NULL**
account_ip                  ** INT 20 NOT NULL**

products
*********
account_id                 ** SMALL INT 5 NOT NULL ZERO **
product_id                 ** INT 5 NOT NULL AUTO INCREMENT **
account_name               ** VARCHAR 25 NOT NULL **
product_item               ** VARCHAR 200 NOT NULL **
product_pic                ** VARCHAR 40 NOT NULL **
product_quinty             ** INT 5 NOT NULL DEFULT 0 **
product_price              ** INT 10 NOT NULL **
purcase_date               ** INT 20 NOT NULL **
note                       ** MEDUIM TEXT NOT NULL **


products_pre_order
******************
account_id                ** SMALL INT 5 NOT NULL ZERO**
account_name              ** VARCHAR 25 NOT NULL **
product_id                ** INT 5 NOT NULL **
product_item              ** VARCHAR 200 NOT NULL **
product_price             ** INT 10 NOT NULL **
note                      ** MEDIUM TEXT NOT NULL **

products_free
*************
account_id               ** SMALL INT 5 NOT NULL ZERO **             
product_count            ** INT 5 NOT NULL **
product_match            ** INT 5 NOT NULL DEFULT 10 **


products_temp
************
temp_id                   ** SMALL INT 5 NOT NULL ZERO AUTO_INCREMENTB **
product_id                ** INT 5 NOT NULL **
product_item              ** VARCHAR 200 NOT NULL **
product_price             ** INT 10 NOT NULL **
purcase_date              ** INT 20 NOT NULL **

paypal
*******

CREATE TABLE paypal_table (
  id int(11) NOT NULL auto_increment,
  payer_id varchar(60) default NULL,
  payment_date varchar(50) default NULL,
  txn_id varchar(50) default NULL,
  first_name varchar(50) default NULL,
  last_name varchar(50) default NULL,
  payer_email varchar(75) default NULL,
  payer_status varchar(50) default NULL,
  payment_type varchar(50) default NULL,
  memo tinytext,
  item_name varchar(127) default NULL,
  item_number varchar(127) default NULL,
  quantity int(11) NOT NULL default '0',
  mc_gross decimal(9,2) default NULL,
  mc_currency char(3) default NULL,
  address_name varchar(255) NOT NULL default '',
  address_street varchar(255) NOT NULL default '',
  address_city varchar(255) NOT NULL default '',
  address_state varchar(255) NOT NULL default '',
  address_zip varchar(255) NOT NULL default '',
  address_country varchar(255) NOT NULL default '',
  address_status varchar(255) NOT NULL default '',
  payer_business_name varchar(255) NOT NULL default '',
  payment_status varchar(255) NOT NULL default '',
  pending_reason varchar(255) NOT NULL default '',
  reason_code varchar(255) NOT NULL default '',
  txn_type varchar(255) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY txn_id (txn_id),
  KEY txn_id_2 (txn_id)
) TYPE=MyISAM;


*/


?>