[thelist] a request for schema review...
rudy
rudy937 at rogers.com
Sat Aug 23 06:44:26 CDT 2003
> 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
More information about the thelist
mailing list