Re: [Hampshire] [OT] BaB Soton Uni register MySQL script

Jim Nicholson wrote:

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

This works...

CREATE DATABASE bab_register;

USE bab_register;

DROP TABLE IF EXISTS attendee;
CREATE TABLE attendee (
        id              INT NOT NULL AUTO_INCREMENT,
        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,
        mac             VARCHAR(50),
                        PRIMARY KEY (id),
                        FOREIGN KEY (mac) REFERENCES macs (mac)
) 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 (mac),
                        FOREIGN KEY (attendee_id) REFERENCES attendee
(attendee_id)
) COMMENT "MAC Address entries";

Damian

--
http://www.diap.org.uk - distributed backup volume management system.

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.