[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