database structure second opinion
Date: 23 May 2004 00:41:58 -0700
Message-ID: <18bdba0a.0405222341.314a90f6_at_posting.google.com>
I am relatively new to database design, and what I am working on is definitely pushing the envelope of my abilities. So I wanted, hopefully, to get a second opinion.
The databse is for accepting photograph submissions for an online gallery. I want to collect info about the photographer and their 1 or 2 photo submissions (no more than 2). Plus I have tables for viewer comments to the photos and a -- I don't know the correct jargon -- for photoSubmissions.
The submission need to be categorized by year, as each year will call for new submissions and thus a new gallery. Submissions must be approved before they will be displayed online. The tables are below.
What I would like to know is how off is it? How would someone that knew more than I approach and build this database? Am I overlooking something obvious or not so obvious?
Your input is supremely appreciated.
create table photographers (
photographerID int(7) unsigned NOT NULL auto_increment primary key,
photoID int(7) NOT NULL,
referID int(7) NOT NULL,
firstName varchar(32) NOT NULL,
lastName varchar(32) NOT NULL,
address1 varchar(32) NOT NULL,
address2 varchar(32),
address3 varchar(32),
city varchar(32) NOT NULL,
state_province varchar(2) NOT NULL,
country varchar(32) NOT NULL,
postalCode varchar(5) NOT NULL,
daytimePhone varchar(14) NOT NULL,
eveningPhone varchar(14),
emailAddress varchar(50) NOT NULL,
websiteAddress varchar(50)
)
create table photos (
photoID int(7) unsigned NOT NULL auto_increment primary key,photo mediumblob NOT NULL, photoTitle tinytext , photoCaption tinytext, photographerID int(7) NOT NULL
)
create table photo_comments (
create table submissions (
photoCommentID int(7) unsigned NOT NULL auto_increment primary key,
photoID int(7) NOT NULL,
comment text NOT NULL,
commentWriterEmail varchar(50) NOT NULL
)
submissionID int(7) unsigned NOT NULL auto_increment primary key,
submissionYear year(4) NOT NULL,
photographerID int(7) NOT NULL,
photoID1 int(7) NOT NULL,
photoID2 int(7),
approved tinyint(1)
)
Received on Sun May 23 2004 - 09:41:58 CEST