Hi,
for a photo contest I’ve set up this schema
CREATE TABLE IF NOT EXISTS pc_contest (
id INT UNSIGNED NOT NULL auto_increment,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
create_date DATE NOT NULL,
max_n_pictures TINYINT DEFAULT 1 COMMENT 'max number of pictures allowed in the contest',
login_type TINYINT DEFAULT 1 COMMENT '1 only fb login,2 fb login + registration,3 only registration',
picture_moderation TINYINT(1) DEFAULT 0 COMMENT '1 all the pictures should be moderate before publishing',
status TINYINT(1) DEFAULT 0 COMMENT '0 unactive 1 active',
header VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_pc_contest_title (title),
UNIQUE KEY unique_pc_contest_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS pc_contestant (
contest_id INT UNSIGNED NOT NULL,
user_fb_id BIGINT UNSIGNED DEFAULT NULL,
firstname VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
join_datetime DATETIME NOT NULL,
join_ip VARCHAR(15) NOT NULL,
hash VARCHAR(40) NOT NULL,
PRIMARY KEY (contest_id,email),
KEY pc_contestant_hash (hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS pc_album(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
contest_id INT UNSIGNED NOT NULL,
contestant_email VARCHAR(255) NOT NULL,
picture VARCHAR(255) NOT NULL,
visible TINYINT(1) DEFAULT 0 COMMENT '1 yes 0 no',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I’m wondering if it’s better using
PRIMARY KEY (contest_id,email)
in pc_contestant or it’s better
to set like
CREATE TABLE IF NOT EXISTS pc_contest (
id INT UNSIGNED NOT NULL auto_increment,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL,
description TEXT DEFAULT NULL,
valid_from DATE NOT NULL,
valid_to DATE NOT NULL,
create_date DATE NOT NULL,
max_n_pictures TINYINT DEFAULT 1 COMMENT 'max number of pictures allowed in the contest',
login_type TINYINT DEFAULT 1 COMMENT '1 only fb login,2 fb login + registration,3 only registration',
picture_moderation TINYINT(1) DEFAULT 0 COMMENT '1 all the pictures should be moderate before publishing',
status TINYINT(1) DEFAULT 0 COMMENT '0 unactive 1 active',
header VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_pc_contest_title (title),
UNIQUE KEY unique_pc_contest_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS pc_contestant (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
contest_id INT UNSIGNED NOT NULL,
user_fb_id BIGINT UNSIGNED DEFAULT NULL,
firstname VARCHAR(255) NOT NULL,
surname VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
join_datetime DATETIME NOT NULL,
join_ip VARCHAR(15) NOT NULL,
hash VARCHAR(40) NOT NULL,
PRIMARY KEY (id),
KEY pc_contestant_hash (hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS pc_album(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
contestant_id INT UNSIGNED NOT NULL,
picture VARCHAR(255) NOT NULL,
visible TINYINT(1) DEFAULT 0 COMMENT '1 yes 0 no',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
pc_album is 1:n
Thanks in advance
Bye