Re: database structure second opinion

From: Todd B <toddkennethbenson_at_yahoo.com>
Date: 24 May 2004 14:16:24 -0700
Message-ID: <ef8e4d1e.0405241316.10e97a9b_at_posting.google.com>


bayon86_at_yahoo.com (cruiserweight) wrote in message news:<18bdba0a.0405240348.65982c8b_at_posting.google.com>...
> 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>...

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

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

I didn't think of this until today, but perhaps your photographers will want some kind of number to attach themselves to when they are associating with you. Kind of like a bank account number. Then, auto_increment works. But if you don't require that, and you need to have only one thing to go by (i.e. you want to have just 'one' field be the primary key), then I would go with an email address as the primary key.

In short, keep using the ID field and use a CHECK constraint to deal with dupes -- or require that everyone has an email address or username of their choice (that must be unique).

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

It's really not all that complicated. In most forms of SQL, the foreign key constraint simply makes sure that an attribute in a table (relation) is forced to be consistent with the attribute of another table (relation). So, if I have:

create table personnel (
 accountname varchar(20) not null primary key,  firstname varchar(50) not null,
 lastname varchar(50) not null,
 email varchar(80),
 department varchar(20) not null references companystructure (department) on update cascade );

//NOTE THE 'department' attribute

create table companystructure (
 department varchar(20) not null primary key );

--> I'm pretty sure those statements work with MySQL <--

That means, in most DBMS's you cannot delete a department if somebody is part of it, and also you cannot insert somebody with a department where the department doesn't already exist in the database (also it means that if I change the department name in table 'companystructure', all personnel referenced to it will similarily change their 'department' attribute). Why is that important? Well, for many people, it probably isn't super critical; But try making sense of the data if you don't have these kind of foreign key constraints (for example, a photographer entering in 'Faust' and 'Fast' as two different submissions, when there really is only a 'Faust' photograph in the photos table).

In addition, I'd say your database schema seems sound. Probably, it doesn't need much modification. Focus on the foreign key constraints.  But don't take my word for it, since I don't know your business rules for the project.

Hope that helps,
Todd Received on Mon May 24 2004 - 23:16:24 CEST

Original text of this message