[thelist] a request for schema review...

[rudy]

>     www.torres.ws/cbs/schema.gif

nice

what diagramming tool?


> CREATE TABLE cast (
>   cast_id       varchar(25)   NOT NULL default '',

good, but in this case i would use an integer, auto_increment

>   first_name    varchar(255)  default NULL,
>   middle_name   varchar(255)  default NULL,
>   last_name     varchar(255)  default NULL,

255 is overkill, but harmless

>   born          varchar(20)   NOT NULL default '0000-00-00',
>   died          varchar(20)   NOT NULL default '0000-00-00',

use date instead of varchar, allow them to be null, and don't use that
abominable "zero date" default

if you are worried about how to store a partial date, e.g. "Feb 1903" then
you may want to consider storing year, month, and day separately, as
smallint, tinyint, tinyint, each nullable

>   PRIMARY KEY  (cast_id,cast_id),

just one will do it

>   KEY cast_id (cast_id),

redundant, the pk is already an index

>   KEY last_name (last_name)

use KEY cast_name_idx (last_name, first_name, middle_name)


> CREATE TABLE appear (
>   appear_id  smallint(12)  NOT NULL default '0',

separate id for this table not required

>   eps_id     smallint(4)   default '0',
>   cast_id    varchar(25)   NOT NULL default 'xx',

both of these should be integer, not null

>   PRIMARY KEY  (appear_id,appear_id),

use PRIMARY KEY (eps_id, cast_id)

>   KEY cast_id (cast_id),
>   KEY appear_id (appear_id),
>   KEY eps_id (eps_id)

replace these with

  KEY cast_eps_idx (cast_id, eps_id)

> DROP TABLE IF EXISTS users;
> CREATE TABLE users (
>   user_id         smallint(4) unsigned NOT NULL auto_increment,

that's okay, except if you want more than 65,000 users, use integer

>   user_name       varchar(10) NOT NULL default '',

i would not bother declaring a default because your app should never allow
it

no password?

>   KEY user_id (user_id),

redundant

>   KEY user_name (user_name)

use a different index name, like KEY user_name_idx (user_name)


> CREATE TABLE showcredits (
>   credit_id  smallint(4) NOT NULL default '0',

not sure what this is for, but if a user can be related to an episode
more than once, then keep credit_id, otherwise ditch it


> CREATE TABLE writers (
>   writer_id  smallint(4) NOT NULL default '0',
>   eps_id     smallint(4) NOT NULL default '0',
>   cast_id    varchar(25) NOT NULL default ''

this table needs more work

are the writers identified in the cast table or user table?


> CREATE TABLE shows (
>   eps_id      smallint(4)  NOT NULL default '0',

definitely integer, probably auto_increment

>   eps_date    date         NOT NULL default '2000-00-00',

i'd let it go null, and don't use a default

>   eps_title   varchar(50)  NOT NULL default 'xx',

may want varchar(255)

>   origwriter  varchar(50)  default NULL,

?

>   user_id     varchar(10)  NOT NULL default '',

if this is a link to the user table, use smallint or integer to match the pk
there

what is the role?  i.e. this episode is related to the user which ... ??

>   KEY eps_id (eps_id)

redundant



hope that helps

rudy