On 13/01/2009, Damian Brasher <lug@???> wrote:
>
> Chris Smith wrote:
>
> > Three problems I can see:
> > 1. using forename and surname for the attendee key will fail if two
> > people have the same name;
>
>
> I agree, small chance but could happen.
>
>
> > 2. MAC address data is duplicated unnecessarily; and
>
>
> Oh yes:)
>
>
> > 3. it only allows one MAC address to be associated with an attendee.
>
>
> Using the approach above make the Primary Key simpler for table macs too.
>
>
> > I would do the following:
> >
> > CREATE TABLE attendee (
> > id INT NOT NULL,
> > forename VARCHAR(50) NOT NULL,
> > surname VARCHAR(50) NOT NULL,
> > email VARCHAR(50),
> > member ENUM('yes','no') NOT NULL,
> > new_attendee ENUM('yes','no') NOT NULL,
> > PRIMARY KEY (id)
> > ) COMMENT "HLUG BaB meeting register Southampton Uni (ECS)";
> >
> > DROP TABLE IF EXISTS macs;
> > CREATE TABLE macs (
> > attendee_id INT NOT NULL,
> > mac VARCHAR(50) NOT NULL,
> > PRIMARY KEY (attendee_id, mac)
> > ) COMMENT "MAC Address entries";
> >
>
The macs table only needs mac as the primary key, you shouldn't have two
attendees with the same mac address.
Make attendee.id AUTO_INCREMENT - that will allow multiple clients to insert
records without tripping over each other.
If you are using a late enough version of MySQL that supports constraints,
add a CONSTRAINT to macs that attendee_id REFERENCES attendee.id.
Jim
