[thelist] a request for schema review...

jsWalter jsWalter at torres.ws
Sat Aug 23 03:32:33 CDT 2003


Thanks to all for the nice "tutorials" on JOINS.

It has helped allot.

Now I would like to request that someone please take a look at the schema I
created (borrowed/modified) to handle a little project I've been toying with
for ages.

on my website, is an image of the layout...

    www.torres.ws/cbs/schema.gif

The line type are wrong, as that I don't know what is right, but I hope you
get the idea.

I'm trying to create a nice database structure in which I can store and
cross-reference lots of information about old radios show (from the 30s to
the 50s), who was in it, who wrote it, etc.

I thought I'd start with the one show I have lots of info on, the CBS Radio
Mystery Theater (1974-1982).

This schema is designed with that show in mind. And after I play with that
for a while, I will expand the structure to include the other 300,000 show I
have *some* info one.

I'd just like to know if I'm barking up the wrong tree on this.

Thanks for your time and enlightenment.

Walter

================================================

Here's the Table structure if it helps...

# Table structure for table cast
#
DROP TABLE IF EXISTS cast;
CREATE TABLE cast (
  cast_id       varchar(25)   NOT NULL default '',
  first_name    varchar(255)  default NULL,
  middle_name   varchar(255)  default NULL,
  last_name     varchar(255)  default NULL,
  image_url     varchar(255)  default NULL,
  soundclip     varchar(255)  default NULL,
  bio           varchar(255)  default NULL,
  born          varchar(20)   NOT NULL default '0000-00-00',
  died          varchar(20)   NOT NULL default '0000-00-00',
  offsite_url   varchar(255)  default NULL,
  PRIMARY KEY  (cast_id,cast_id),
  KEY cast_id (cast_id),
  KEY last_name (last_name)
);

# Table structure for table appear
#
DROP TABLE IF EXISTS appear;
CREATE TABLE appear (
  appear_id  smallint(12)  NOT NULL default '0',
  eps_id     smallint(4)   default '0',
  cast_id    varchar(25)   NOT NULL default 'xx',
  PRIMARY KEY  (appear_id,appear_id),
  KEY cast_id (cast_id),
  KEY appear_id (appear_id),
  KEY eps_id (eps_id)
);

#
# --------------------------------------------------------
#
# Table structure for table users
#
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  user_id         smallint(4) unsigned NOT NULL auto_increment,
  user_name       varchar(10) NOT NULL default '',
  user_email      varchar(50) NOT NULL default '',
  user_url        varchar(50) default NULL,
  user_real_name  varchar(50) default NULL,
  user_rights     smallint(4) unsigned NOT NULL '0'
  PRIMARY KEY  (user_id),
  KEY user_id (user_id),
  KEY user_name (user_name)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# --------------------------------------------------------
#
# Table structure for table showcredits
#
DROP TABLE IF EXISTS showcredits;
CREATE TABLE showcredits (
  credit_id  smallint(4) NOT NULL default '0',
  eps_id     smallint(4) NOT NULL default '0',
  user_id    varchar(10) NOT NULL default '',
  PRIMARY KEY  (credit_id),
  KEY credit_id (credit_id)
);

#
# --------------------------------------------------------
#
# Table structure for table writers
#
DROP TABLE IF EXISTS writers;
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 ''
);

#
# --------------------------------------------------------
#
# Table structure for table shows
#
DROP TABLE IF EXISTS shows;
CREATE TABLE shows (
  eps_id      smallint(4)  NOT NULL default '0',
  eps_date    date         NOT NULL default '2000-00-00',
  eps_title   varchar(50)  NOT NULL default 'xx',
  comment     varchar(255) default NULL,
  eps_plot    longtext,
  origwriter  varchar(50)  default NULL,
  user_id     varchar(10)  NOT NULL default '',
  PRIMARY KEY  (eps_id),
  KEY title (eps_title),
  KEY eps_id (eps_id)
);

#
# --------------------------------------------------------




More information about the thelist mailing list