#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    vancouver, bc
    Posts
    145
    Rep Power
    15

    newbie with postgres


    Hi,

    I'm creating my first web app in postgres and Php.

    I just finished my schema and I think I got it normalized but i'm not sure if everything else is right. I was using mysql for a while so this schema might be wrong.

    can someone help me take a once over it and see if I need to correct anything?

    thanks.

    Code:
    CREATE TABLE admin (
      adminId				SMALLINT(1) PRIMARY KEY, 
      lastLoggedIn	TIMESTAMP, 
      firsName 			VARCHAR(20), 
      middleName 		VARCHAR(4), 
      lastName 			VARCHAR(20), 
      username 			VARCHAR(10), 
      password 			VARCHAR(6), 
      userHash 			VARCHAR(32), 
      passHash 			VARCHAR(32), 
      email 				VARCHAR(25), 
    );
    
    CREATE TABLE members (
    	memberId 			MEDIUMINT(8) PRIMARY KEY,  
      dateJoined 		TIMESTAMP, 
      firsName 			VARCHAR(20), 
      middleName 		VARCHAR(4), 
      lastName 			VARCHAR(20), 
      username 			VARCHAR(10), 
      password 			VARCHAR(6), 
      userHash 			VARCHAR(32), 
      passHash 			VARCHAR(32), 
      avatarImageUrl MEDIUMINT(50), 
      email 				VARCHAR(25), 
      bdayMonth 		SMALLINT(2), 
      bdayDay 			SMALLINT(2), 
      bdayYear			MEDIUMINT(4), 
      lastLoggedIn	TIMESTAMP,
      city 					VARCHAR(15), 
      stateProvince SMALLINT(3) FOREIGN KEY, #stateProvince.stateProvinceId
      country 			SMALLINT(3) FOREIGN KEY, #country.countryId
      websiteUrl 		VARCHAR(60),
      ImIcq					VARCHAR(30),
      ImMsn 				VARCHAR(30),
      ImAim 				VARCHAR(30),
      ImYahoo 			VARCHAR(30),
      numberOfComments SMALLINT(4),
      numberOfThemes SMALLINT(4),
      numberOfWallpapers SMALLINT(4),
      numberOfGames SMALLINT(4),
      numberOfRingtones SMALLINT(4),
      interests 		VARCHAR(255), 
      sex 					SMALLINT(1),
      favTools 			VARCHAR(255),
      rank 					SMALLINT(2) FOREIGN KEY, #ranks.rankId
      userStatus 		SMALLINT(1)
      favouriteItem SMALLINT(2) FOREIGN KEY, #favItems.favItemId
      favouriteFriends SMALLINT(2) #favFriends.favFriendId
      rating 				SMALLINT(2) #ratings.ratingId
    );
    
    CREATE TABLE items (
      itemId 				INT(10) PRIMARY KEY,
      memberId			INT(10) FOREIGN KEY, # members.memberId
      itemType 			VARCHAR(10), 
      dateSubmitted TIMESTAMP,
      itemName			VARCHAR(50),
      itemDescription VARCHAR(255),
      downloads			VARCHAR(10),
      smImageUrl 		VARCHAR(50),
      bigImageUrl 	VARCHAR((50),
      staffReview 	VARCHAR(255),
      staffRatings 	SMALLINT(2),
      phoneCompatibility INT(10) FOREIGN KEY, #phone.phoneId
      otherkeywords VARCHAR(255),
      categoryId 		SMALLINT(2), # dunno
      filesize 			VARCHAR(10),
      typeOfRingtone CHAR(4),
      resolution 		VARCHAR(15),
      itemFormat 		VARCHAR(4),
    );
    
    CREATE TABLE phones (
      phoneId 			INT(10) PRIMARY KEY,
      phoneOwner 		INT(10) FOREIGN KEY, # members.memberId
      phoneBrand 		VARCHAR(20),
      phoneModel 		VARCHAR(20),
      phonePageUrl 	VARCHAR(50),
      shelfLife 		SMALLINT(1),
    );
    
    CREATE TABLE reviews (
      reviewId 			INT(10) PRIMARY KEY,
      reviewAuthorId INT(10) FOREIGN KEY, # members.memberId
      itemId 				INT(10) FOREIGN KEY, # items.itemId
      dateSubmitted TIMESTAMP,
      reviewBody 		VARCHAR(255),
      rating 				SMALLINT(2) FOREIGN KEY, # ratings.ratingId
    );
    
    CREATE TABLE favItem (
      favItemId			INT(10) PRIMARY KEY,
      memberId			INT(10) FOREIGN KEY, # members.memberId
    );
    
    CREATE TABLE favFriends (
      favFriendId		INT(10) PRIMARY KEY,
      itemId 				INT(10) FOREIGN KEY, #items.itemId
    );
    
    CREATE TABLE ratings (
      ratingId 			VARCHAR(2) PRIMARK KEY,
      ratingName 		VARCHAR(20),
      ratingImage 	VARCHAR(20),
    );
    
    CREATE TABLE category (
      categoryId 		SMALLINT(2) PRIMARY KEY,
      categoryName 	VARCHAR(30),
    );
    
    CREATE TABLE compatibles (
    	compatibleId SMALLINT(2) PRIMARY KEY,
      itemId INT(10) FOREIGN KEY,
      phoneId INT(10) FOREIGN KEY,
    );

    um....i dunno how to fix the tabs....
    I know nothing
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    vancouver, bc
    Posts
    145
    Rep Power
    15
    anyone help me out? and how do you do auto incrementing ? and do I need to specify NOT NULL ?
  4. #3
  5. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    As far as autoincrementing goes, check out the SERIAL type in the docs. Basically you just do

    CREATE TABLE
    {
    auto_id SERIAL,
    .
    .
    .
    };

    which all it really does under the hood is set the auto_id field to be an int field with the default being a sequence.

    Also you may want to change the tinyint fields to just int fields because queries may not use indexes on them unless properly casted of you might have to put it in quotes. Plus I don't believe it ends up saving much space (reference here).

    Also, I've only briefly glanced at the schema, but depending on your needs you may not need to have separate tables for admins and users, just have all the info they have in common in one main user table and then have side tables referencing the userID with all the side info they don't share and also denoting what type of user they are, then again I'm not exactly sure how the info is being used.

    BTW, welcome to the postgres community, always nice to hear of new people using it.

    PostgreSQL, it's what's for dinner...
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    vancouver, bc
    Posts
    145
    Rep Power
    15
    thanks,
    i'm going to add a field in the member's table for type of members (admin, users, etc). but what about "NULL" do I have to add those in? and in my items table, i have about 4 different items and different items might not fill up al the fields. is it ok to have some fields empty?

    in mysql, there are different table types. are there anythings like that in pg ? and what about constraints? should I add them in?
  8. #5
  9. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Regarding not null fields and constraints. I usually try and design all my tables to not allow nulls and provide I default value where applicable.

    One thing that you should know though I believe I read that in MySQL NULL is treated to be equal to both 0 and a zero length string (I may be wrong on this, so if I am disregard, or this may be deprecated behavior), but this is not the case in Postgres.

    -b
    PostgreSQL, it's what's for dinner...
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Location
    vancouver, bc
    Posts
    145
    Rep Power
    15
    thanks,i'll revise my schema. I know I'll be back with more questions about PG once I get started with it.

IMN logo majestic logo threadwatch logo seochat tools logo