List:General Discussion« Previous MessageNext Message »
From:ddevaudreuil Date:February 10 2009 6:19pm
Subject:Re: db setup - correction
View as plain text  
PJ <af.gourmet@stripped> wrote on 02/10/2009 12:44:04 PM:

> -- -----------------------------------------------------
> -- Table `books`
> -- -----------------------------------------------------
> CREATE  TABLE IF NOT EXISTS `books` (
>   `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT ,
>   `title` VARCHAR(148) NULL ,
>   `sub_title` VARCHAR(90) NULL ,
>   `descr` TINYTEXT NULL ,
>   `comment` TEXT NULL ,
>   `bk_cover` VARCHAR(32) NULL ,
>   `publish_date` YEAR NULL ,
>   `ISBN` BIGINT(13) NULL ,
>   `language_id` INT NULL ,
>   PRIMARY KEY (`id`) ,
>   INDEX `fk_books_language` (`language_id` ASC) ,
>   CONSTRAINT `fk_books_language`
>     FOREIGN KEY (`language_id` )
>     REFERENCES `biblane`.`language` (`id` )
>     ON DELETE NO ACTION
>     ON UPDATE NO ACTION)
> ENGINE = InnoDB;
>
May I make one sugggestion? I noticed that the books.id column is defined
as SMALLINT UNSIGNED. Unless your database is going to stay quite small,
that is really going to limit the number of books. This column is used as a
FK in a number of your other tables and if you later on have to change the
data type to make it bigger, you'll have to change all the related tables.
If I remember correctly, I had to drop all the FK constraints that
referenced this column, do the alter tables, and then recreate the FK
constraints.  Save yourself the hassle and make it at least an Integer, if
not a BIGINT (unsigned).

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Donna

Thread
db setupPJ9 Feb
  • Re: db setupOlaf Stein9 Feb
  • Re: db setup - correctionOlaf Stein9 Feb
    • Re: db setup - correctionPJ9 Feb
      • Re: db setup - correctionPeter Brawley9 Feb
        • Re: db setup - correctionOlaf Stein9 Feb
        • Re: db setup - correctionPJ9 Feb
          • Re: db setup - correctionOlaf Stein9 Feb
          • Re: db setup - correctionPeter Brawley9 Feb
            • Re: db setup - correctionPJ10 Feb
              • Re: db setup - correctionPeter Brawley10 Feb
                • Re: db setup - correctionPJ10 Feb
                  • Re: db setup - correctionPeter Brawley10 Feb
                  • Re: db setup - correctionddevaudreuil10 Feb
                    • Size limitation of user variable?Bryan Cantwell10 Feb
                      • Re: Size limitation of user variable?Baron Schwartz10 Feb
                        • Re: Size limitation of user variable?Johan De Meersman11 Feb
                          • Re: Size limitation of user variable?Baron Schwartz11 Feb
Re: db setup - correctionPJ10 Feb