vjwilson 21 Newbie Poster

I am updating a conference website, and I've come across a design problem that has me stumped. I have tables for:
-a company's account
-person (each company can have many persons in its account)
-conference sections (for example, 1-day, 2-day, or 3-day attendance)
-a lookup table relating people to sections (this would allow a company to have 3 people attend this year, but then only send 2 of those 3 next year, for instance)

Here are abbreviated CREATE statements:

CREATE  TABLE IF NOT EXISTS `accounts` (
  `account_id` INT(10) NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(255) NOT NULL ,
  `password` CHAR(64) NULL ,
  `email` VARCHAR(255) NOT NULL ,
  `company` VARCHAR(255) NULL ,
  PRIMARY KEY (`account_id`) )  (... remainder of CREATE statement omitted)


CREATE  TABLE IF NOT EXISTS `persons` (
  `person_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `firstname` VARCHAR(45) NOT NULL ,
  `lastname` VARCHAR(45) NOT NULL ,
  `jobtitle` VARCHAR(255) NULL DEFAULT NULL ,
  `phone` CHAR(12) NULL DEFAULT NULL ,
  `email` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`person_id`) ,
  INDEX `fk_person_account` (`account_id` ASC) ,
  CONSTRAINT `fk_person_account`
    FOREIGN KEY (`account_id` )    (... remainder of CREATE statement omitted)

CREATE  TABLE IF NOT EXISTS `sections` (
  `section_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NOT NULL ,
  `startdate` DATE NULL ,
  `enddate` DATE NULL ,
  `base_price` INT(10) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`section_id`)     (... remainder of CREATE statement omitted)

CREATE  TABLE IF NOT EXISTS `person_attending_section` (
  `person_id` INT(10) UNSIGNED NOT NULL ,
  `section_id` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`person_id`, `section_id`) ,
  INDEX `fk_person_has_section_person1` (`person_id` ASC) ,
  INDEX `fk_person_has_section_section1` (`section_id` ASC) ,
  CONSTRAINT `fk_person_has_section_person1`
    FOREIGN KEY (`person_id` )
    REFERENCES `persons` (`person_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_person_has_section_section1`
    FOREIGN KEY (`section_id` )
    REFERENCES `sections` (`section_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Here's the problem: I'd like to have an `orders` table that takes an account's `persons` who have been added to the `persons_attending_section` for this year's sections and allows the account holder to "check out" and pay for all those registrations. (Refunds, if necessary, would be handled off-line.) Then, if more people from that company want to attend, they could be added and "checked out" as a second, and separate order. After the conference is over, those orders would be locked. An account could copy an order from the previous year, but that will involve checking out again, and it will create another order for the account.

I've thought about something like this:

CREATE  TABLE IF NOT EXISTS `orders` (
  `orders_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `account_id` INT NOT NULL ,
  `created_on` DATETIME NOT NULL ,
  `person_id` INT(10) UNSIGNED NOT NULL ,
  `section_id` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`orders_id`) )

with appropriate foreign keys, and just copying over person_id and section_id from all the appropriate rows of the `person_attending_section` table, but that seems inelegant and error-prone.

Should I just create an artificial, auto-increment primary key for the `person_attending_section` table and have the other two keys just be foreign keys--then I could treat that table like an "ordered items" table from a static e-commerce style database?

Any suggestions would be appreicated.