Re: database structure second opinion

toddkennethbenson_at_yahoo.com (Todd B) wrote in message news:<ef8e4d1e.0405231456.60335a20_at_posting.google.com>...
> 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).

There is nothing to prevent duplicate entries! Yikes. What would the best way to prevent dupes be? Or any way, not just the best, for that matter. Perhaps a FirstNameLastNameEmail field? Could I then make that the unique primary key instead of the auto_increment?

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

the whole foreign key is a bit beyond me at the moment. let me read up and hopefully it will makes more sense soon.

> Hope that helps a teensy bit.

Much. Thanks.

> Todd
Received on Mon May 24 2004 - 13:48:10 CEST