[thelist] [mysql] table structure review request

[Ken Schaefer]

You should use ER (or EER) modelling to determine whether the table
structure is "good" or not.

For field sizes, the fields that are related to parent tables should
be the same size as the fields in the parent tables.

Cheers
Ken

On Mon, 16 Aug 2004 18:07:51 -0700 (PDT), Tom Dell'Aringa
<pixelmech at yahoo.com> wrote:
> Hello, another mySQL question - this time on the structure of my table/db. This is going to be a
> long term project, so I want to make sure I've got a good structure from the start. The table is
> for a certain type of book. Here it is:
> 
> #
> # Table structure for table `book`
> #
> 
> CREATE TABLE `book` (
>  `bookID` int(5) NOT NULL auto_increment,
>  `booktitle` varchar(75) NOT NULL default '',
>  `publisherID` int(5) NOT NULL default '0',
>  `stripyears` varchar(10) NOT NULL default '',
>  `printings` varchar(5) NOT NULL default '1',
>  `yearprinted` varchar(4) NOT NULL default '',
>  `size` varchar(10) NOT NULL default '',
>  `formatID` int(5) NOT NULL default '0',
>  `rarityID` int(5) NOT NULL default '0',
>  `nmvalue` int(10) default '0',
>  `notes` mediumblob,
>  `incommon` mediumblob,
>  PRIMARY KEY  (`bookID`)
> ) TYPE=MyISAM AUTO_INCREMENT=1 ;
> 
> publisherID, formatID and rarityID are all foreign keys of other tables to avoid data errors. What
> I'm not sure of is do I have the values for each type of data correct (ie int(5) for foreign
> keys), or do I have any glaring errors?
> 
> TIA
> 
> Tom