database structure second opinion

bayon86_at_yahoo.com (cruiserweight) wrote in message news:<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)
> )

In this table, what's to prevent you from entering the same photographer twice? Some people swear by random integer primary keys, but it will depend on how much you care about the data integrity. Will you be doing reports on this some day (i.e. will you have to ask a question like, how many submissions have I received from photographers that a have a website, or how many accepted submissions came from New York).

>
> 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
> )

Hopefully you have a DBMS that supports foreign keys. If so, you would want to change 'photographerID int(7) NOT NULL' to -

photographerID int(7) NOT NULL references photographers(photographerID) on update cascade

>
> create table photo_comments (
> photoCommentID int(7) unsigned NOT NULL auto_increment primary key,
> photoID int(7) NOT NULL,
> comment text NOT NULL,
> commentWriterEmail varchar(50) NOT NULL
> )
>
> create table submissions (
> 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)
> )

Same thing for photoID in table 'photo_comments', and also photographerID in table 'submissions'.

Also, personally, I would leave photoID1 and photoID2 out of submissions, and just have a column called 'photo' that is a foreign key to photoID in table photos. Instead of submissionID being the primary key (and, if you are going to stick with auto_increment primary keys), I would go with:

primary key (photographerID, photo)

Or, if a photographer can submit the same photo more than once:

primary key (photographerID, photo, submissionDate)

But I don't know if MySQL supports multiple keys in a table (I'm just assuming you are using MySQL, because of the auto_increment keyword).

Hope that helps a teensy bit.

Todd Received on Mon May 24 2004 - 00:56:02 CEST