1 Reply Latest reply on Jul 9, 2009 11:42 AM by greg2

    why do we have different schema for jiverating table in mysql and postgres

    shekher1983

      -----------------------mysql schema-----------------------------------------


       

       

      # jiveRatingType: Rating types.
      # ---------------------------------------------------------------------------
      CREATE TABLE jiveRatingType (
          score        INT NOT NULL,
          description  TEXT NOT NULL,
          CONSTRAINT jiveRatingType_pk PRIMARY KEY (score)
      ) ENGINE = InnoDB CHARACTER SET utf8;
      # ---------------------------------------------------------------------------
      # jiveRating: Rating data table.
      # ---------------------------------------------------------------------------
      CREATE TABLE jiveRating (
          objectID      BIGINT NOT NULL,
          objectType    INT NOT NULL,
          userID        BIGINT NULL,
          score         INT NOT NULL,
          creationDate  BIGINT NULL,
          INDEX jvRtng_usrID_idx (userID),
          INDEX jvRtng_ID_Type_idx (objectID,objectType)
      ) ENGINE = InnoDB CHARACTER SET utf8;

       


      -----------------------postgress schema-----------------------------------------


       

      -- ---------------------------------------------------------------------------
      -- jiveRatingType: Rating types.
      -- ---------------------------------------------------------------------------
      CREATE TABLE jiveRatingType (
          score        INTEGER NOT NULL,
          description  VARCHAR(1000) NOT NULL,
          CONSTRAINT jiveRatingType_pk PRIMARY KEY (score)
      );

       

      -- ---------------------------------------------------------------------------
      -- jiveRating: Rating data table.
      -- ---------------------------------------------------------------------------
      CREATE TABLE jiveRating (
          objectID      BIGINT NOT NULL,
          objectType    INTEGER NOT NULL,
          userID        BIGINT,
          score         INTEGER NOT NULL,
          creationDate  BIGINT
      );
      CREATE INDEX jvRtng_usrID_idx ON jiveRating (userID);
      CREATE INDEX jvRtng_ID_Type_idx ON jiveRating (objectID,objectType);

      ALTER TABLE jiveRating ADD CONSTRAINT jiveRtg_score_fk FOREIGN KEY (score) REFERENCES jiveRatingType;

       

       

      can any body please help  me 2 understand the need for the 2 different schemas for the table jiverating in mysql and postgress the foregin key constraints are there for the score coloum in jiverating table i dont understand the need for the foregn key constraints in postgress table where as it doesnt exist in mysql schema.

       

      is this upgrade realted problem?

      actually i wanna to add custom score based on system property that may or may not exist in jiveratingtype table

       

       

      Regards,

      Chandra