SEARCH YOUR SOLUTION HERE  

Composite primary key schema


for a photo contest I've set up this schema
PHP Code: CREATETABLEIFNOTEXISTSpc_contest(
idINTUNSIGNEDNOTNULLauto_increment,
titleVARCHAR(255)NOTNULL,
slugVARCHAR(255)NOTNULL,
descriptionTEXTDEFAULTNULL,
valid_fromDATENOTNULL,
valid_toDATENOTNULL,
create_dateDATENOTNULL,
max_n_picturesTINYINTDEFAULT1COMMENT'maxnumberofpicturesallowedinthecontest',
login_typeTINYINTDEFAULT1COMMENT'1onlyfblogin,2fblogin+registration,3onlyregistration',
picture_moderationTINYINT(1)DEFAULT0COMMENT'1allthepicturesshouldbemoderatebeforepublishing',
statusTINYINT(1)DEFAULT0COMMENT'0unactive1active',
headerVARCHAR(255)DEFAULTNULL,
PRIMARYKEY (id),
UNIQUEKEYunique_pc_contest_title(title),
UNIQUEKEYunique_pc_contest_slug(slug)
)ENGINE=InnoDB DEFAULTCHARSET=utf8;

CREATETABLEIFNOTEXISTSpc_contestant(
contest_idINTUNSIGNEDNOTNULL,
user_fb_idBIGINTUNSIGNEDDEFAULTNULL,
firstnameVARCHAR(255)NOTNULL,
surnameVARCHAR(255)NOTNULL,
emailVARCHAR(255)NOTNULL,
join_datetimeDATETIMENOTNULL,
join_ipVARCHAR(15)NOTNULL,
hashVARCHAR(40)NOTNULL,
PRIMARYKEY (contest_id,email),
KEYpc_contestant_hash(hash)
)ENGINE=InnoDB DEFAULTCHARSET=utf8;

CREATETABLEIFNOTEXISTSpc_album(
idINTUNSIGNEDNOTNULLAUTO_INCREMENT,
contest_idINTUNSIGNEDNOTNULL,
contestant_emailVARCHAR(255)NOTNULL,
pictureVARCHAR(255)NOTNULL,
visibleTINYINT(1)DEFAULT0COMMENT'1yes0no',
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
I'm wondering if it's better using
PRIMARY KEY (contest_id,email)
in pc_contestant or it's better
to set like
PHP Code: CREATETABLEIFNOTEXISTSpc_contest(
idINTUNSIGNEDNOTNULLauto_increment,
titleVARCHAR(255)NOTNULL,
slugVARCHAR(255)NOTNULL,
descriptionTEXTDEFAULTNULL,
valid_fromDATENOTNULL,
valid_toDATENOTNULL,
create_dateDATENOTNULL,
max_n_picturesTINYINTDEFAULT1COMMENT'maxnumberofpicturesallowedinthecontest',
login_typeTINYINTDEFAULT1COMMENT'1onlyfblogin,2fblogin+registration,3onlyregistration',
picture_moderationTINYINT(1)DEFAULT0COMMENT'1allthepicturesshouldbemoderatebeforepublishing',
statusTINYINT(1)DEFAULT0COMMENT'0unactive1active',
headerVARCHAR(255)DEFAULTNULL,
PRIMARYKEY (id),
UNIQUEKEYunique_pc_contest_title(title),
UNIQUEKEYunique_pc_contest_slug(slug)
)ENGINE=InnoDB DEFAULTCHARSET=utf8;

CREATETABLEIFNOTEXISTSpc_contestant(
idINTUNSIGNEDNOTNULLAUTO_INCREMENT,
contest_idINTUNSIGNEDNOTNULL,
user_fb_idBIGINTUNSIGNEDDEFAULTNULL,
firstnameVARCHAR(255)NOTNULL,
surnameVARCHAR(255)NOTNULL,
emailVARCHAR(255)NOTNULL,
join_datetimeDATETIMENOTNULL,
join_ipVARCHAR(15)NOTNULL,
hashVARCHAR(40)NOTNULL,
PRIMARYKEY (id),
KEYpc_contestant_hash(hash)
)ENGINE=InnoDB DEFAULTCHARSET=utf8;

CREATETABLEIFNOTEXISTSpc_album(
idINTUNSIGNEDNOTNULLAUTO_INCREMENT,
contestant_idINTUNSIGNEDNOTNULL,
pictureVARCHAR(255)NOTNULL,
visibleTINYINT(1)DEFAULT0COMMENT'1yes0no',
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
pc_album is 1:n

Posted On: Thursday 25th of October 2012 09:58:46 PM Total Views:  357
View Complete with Replies




Related Messages:

Foreign composite key referencing same column   (109 Views)
I have one table persons and a link table couples : persons(person_id, name) pk = person_id link table to represent a many to many relationship between persons : couples(person_id_1, person_id_2) pk = person_id_1, person_id_2 (composite key) There's a constraint to avoid inverse duplicates in the link table couples Example : persons values : (1, John) (2, Anne) couples values : (1, 2) (2, 1) The 2 rows in 'couples' are duplicates for me.
id or user_id as primary key   (107 Views)
hi I am wondering whether there is any advantage of using id as a primary key in a users table, versus something more descriptive like userid or user_id.
How to choose my primary keys?   (96 Views)
Hi all, I'm working on a webshop system where product and category data of multiple shops are kept in these tables: Code: CREATE TABLE `category` ( `categoryID` int(10) unsigned NOT NULL AUTO_INCREMENT, `shopID` int(10) unsigned NOT NULL, `parentID` int(10) unsigned NOT NULL, `title` varchar(100) NOT NULL, `urlTitle` varchar(100) NOT NULL, `description` varchar(255) NOT NULL, `path` varchar(255) NOT NULL, `order` int(10) unsigned NOT NULL, PRIMARY KEY (`categoryID`), UNIQUE KEY `path` (`path`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ; CREATE TABLE `product` ( `productID` int(10) unsigned NOT NULL AUTO_INCREMENT, `shopID` int(10) unsigned NOT NULL, `catalogueID` int(10) unsigned NOT NULL, `price` float unsigned NOT NULL, PRIMARY KEY (`productID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ; CREATE TABLE `product_category` ( `productID` int(10) unsigned NOT NULL, `categoryID` int(10) unsigned NOT NULL, `order` int(10) unsigned NOT NULL, PRIMARY KEY (`productID`,`categoryID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Products can be assigned to multiple categories. categoryID and productID are both PRIMARY keys and shopID is a foreign key for the product and category table. I'm not sure why, but suddenly I started doubting that maybe this was a better solution: Take shopID and productID as a compound PRIMARY key and make productID auto-increment. Do the same for the category table, but with categoryID. This way, productID and categoryID auto-increment per shopID. Lastly, add shopID to the product_category table and make a compound PRIMARY key with shopID, productID and categoryID. I'm clearly missing some fundamental knowledge and experience in database design here Can somebody point out to me what is the best approach and why For starters, I think it's better to have shopID in every table, because you can directly query for all results in a given shop... This is what the new db scheme would look like: Code: CREATE TABLE `category` ( `shopID` int(10) unsigned NOT NULL, `categoryID` int(10) unsigned NOT NULL AUTO_INCREMENT, `parentID` int(10) unsigned NOT NULL, `title` varchar(100) NOT NULL, `urlTitle` varchar(100) NOT NULL, `description` varchar(255) NOT NULL, `path` varchar(255) NOT NULL, `order` int(10) unsigned NOT NULL, PRIMARY KEY (`shopID`,`categoryID`), UNIQUE KEY `path` (`path`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ; CREATE TABLE `product` ( `shopID` int(10) unsigned NOT NULL, `productID` int(10) unsigned NOT NULL AUTO_INCREMENT, `catalogueID` int(10) unsigned NOT NULL, `price` float unsigned NOT NULL, PRIMARY KEY (`shopID`,`productID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ; CREATE TABLE `product_category` ( `shopID` int(10) unsigned NOT NULL, `productID` int(10) unsigned NOT NULL, `categoryID` int(10) unsigned NOT NULL, `order` int(10) unsigned NOT NULL, PRIMARY KEY (`shopID`,`productID`,`categoryID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Grtz, M.
Use primary row as noimage.png vs checking with php   (100 Views)
I have one simple question. What is better. To put in table a field primary as noimage.png or to use empty fields and checking with php if field is empty and than assigning value of $image to noimage.png And is it better to use for such example null or not null Thank you
differnce betwwen primarykey and forignkey   (103 Views)
hello , i want clear expli
remove unique , primary property   (132 Views)
, i have table customers , and in this table there is 'username' field and its unique , and i want to remove this property , how can i please regards
Force primary ID   (93 Views)
How to force the primary key for a table to begin at a set Primary Id and not standard count (1,2,3,4,5..10,11,12) etc. Pretty much how to force it being on number I would like to begin.
primary key field: varchar(32) versus sequence   (93 Views)
I got in the habit of coding primary key fields as varchar(32) that was generated by an md5() since it was a public site and I wanted to avoid people guessing record numbers (for other reasons). Is there a performance problem with doing that If so, how do you tackle the same concern
Newbie primary key foreign key problem.   (104 Views)
I have two tables: Users Code: user_ID (INT) 5 userName VARCHAR(100) *userLicenseCode INT(5) License Codes Code: license_ID INT(5) license_Code VARCHAR(50) I am having trouble in figuring out how to set the primary and foreign keys for these tables, relating linking the user account to the license key. Here is the use case: The user has a license key. They register using their name and the license key. The form is submitted. The system checks that the license key exists in the License Codes table, if it does, the user table is populated with the user's name. Now the system sets the license_ID from the License Codes table which is a primary key to reference the userLicenseCode field which is a foreign key in the Users table. //end use case My problem is that the userLicenseCode field is initially set to null, but since it is a foreign key did not update in my previous implementation. Can anyone comment on how this schema looks now I'm just after some direction really. Many
Adding primary key to a table with 1 million records   (92 Views)
Hi , We have a requirement where we need to add a primary key on a a couple of columns to a table with 1 million records. We are using inno DB . But what we find is adding a primary key is extremely slow (takes about 1 hour in all) I have tried the ALTER IGNORE TABLE ADD PRIMARY KEY(COLUMN) option , but we are not allowed to use it due to PMD violations. So is there a way of optimizing the ADD PRIMARY KEY on a table with 1 million records.
Can I have primary key only in table and varchar   (98 Views)
Hi I usually use int for primary key and then say name for the next field but I have to use mutliple forms on an international site each country has different products and its all down in pure html except for the warranty registration pages. Each country is like it's own website so I can't have one form for all. The form goes into the database and each form has it's country name. The country field is a foreign field for contacts. It is varchar to have the name showing in the form because I have to add a hidden field of the country name into the form to go into the database: CREATE TABLE IF NOT EXISTS contacts ( contactid int(11) NOT NULL AUTO_INCREMENT, name varchar(120) DEFAULT NULL, name2 varchar(120) DEFAULT NULL, name3 varchar(120) DEFAULT NULL, name4 varchar(120) DEFAULT NULL, name5 varchar(120) DEFAULT NULL, name6 varchar(120) DEFAULT NULL, name7 varchar(120) DEFAULT NULL, name8 varchar(120) DEFAULT NULL, email varchar(120) DEFAULT NULL, country varchar(120) DEFAULT NULL, message text, contactdate varchar(20) DEFAULT NULL, expires varchar(40) DEFAULT NULL, PRIMARY KEY (contactid) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; CREATE TABLE IF NOT EXISTS countries ( country varchar(120) NOT NULL DEFAULT '', PRIMARY KEY (country) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table 'countries' -- INSERT INTO countries (country) VALUES ('Argentina and South America'), ('Australia'), ('Austria'), ('Belgium'), ('China'), ('France'), ('Germany'), ('Ireland'), ('Japan'), ('Netherlands'), ('New Zealand'), ('South Africa'), ('Switzerland'), ('UK'); However if I try and update this table country I get invalid errors when I am trying to update the table sprintf("UPDATE countries SET country=%s WHERE country=%s", Will it only update if I have int for the primary key Sorry have searched google for hours and can't get an answer I am looking for....
How do I drop a primary key that has a foreign key in another table?   (107 Views)
I'm trying to drop a table I don't need but, it's primary key is a foreign key in another table. I tried to drop the foreign key but I get an error. I tried to drop the primary key but it won't let me. I'm using InnoDB. Can someone please help me with this Thank you.
How can I allow for duplicate ids in the primary key field   (127 Views)
I have items that are being put in to my database. I can enter one item but then when I want to enter a new item I get a duplicate key error. The reason I need to have the same id for items is that the items correspond to clients and each client has different items. I thought I had this set up correctly but setting both of the fields in the DB table to be the primary keys but that is not working. Basically this is what my DB needs to look like. soq_references_id-------bullet ----------------1-------bullet 1 ----------------1-------bullet 2 ----------------1-------bullet 3 It will continue down the line with the different ids. The Ids are coming from a table called soq_references, so each soq_references_id relates to a clietns ID and puts the bullets under the right client. Cliff notes: I need to know how to allow for duplicate soq_references_id's. if you need more information please ask, thank you in advance for the help. , 9-bullet is just saying the id is "9" and the text being put in is "bullet". Im using CRUD so that's how it displays it.
Resetting primary key to 0 in phpmyadmin   (123 Views)
Ok I have two tables and two primary keys one on each table. My hosting saddly doesnt suport InnoDB tables so the only way i have to match records between tables is the primary keys on each table. The problem I have is resetting them both back to 0 is there any way to do this in phpmyadmin
Page 2 - How to know which one is primary column   (104 Views)
'Show' does not support 'where' clause. Run this query SHOW KEYS FROM tbl_name WHERE Key_name = 'PRIMARY' you will get MySql error. 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE Key_name = 'PRIMARY'' at line 1 '
Merging two data sets that have primary keys   (133 Views)
I am trying to merge two tables into one. These tables are the exact same structure and they both have primary keys. The numbers in these primary keys (INT) do conflict with each other. I am want to do a mysqldump on each table that can be used to merge them into one. I don't care what the primary key values are equal to after they are merged together. It can reindex the whole data set if it wants to I just don't want to get an error when I try to import the second sql file. INSERT IGNORE gets me around the error but it doesn't move all of the data in. Is there a way to either drop the primary key field out of the mysqldump or is there a way to get the server to just grab the next AUTO_INCREMENT value when there is a conflict
Can a foreign key be a primary key too?   (111 Views)
I am trying to do this but i'm getting this error: #1005 - Can't create table '.\startbron\m_shoutbox.frm' (errno: 150) This is the code, the parent and child table: Code: CREATE TABLE m_categorien ( cat_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, pagina VARCHAR(64) NOT NULL, ... PRIMARY KEY(cat_id), INDEX (pagina), FOREIGN KEY (pagina) REFERENCES m_paginas(pagina) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB; CREATE TABLE m_link_groep ( cat_id INTEGER UNSIGNED NOT NULL, ... PRIMARY KEY(cat_id), FOREIGN KEY (cat_id) REFERENCES categorien(cat_id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = INNODB; The primary key from the parent table allready uniquely indentifies the records so i have no reason to create a seperate primary key. As you can see; the parent table is also a child of another table.
Mysql primary key problems   (109 Views)
in mysql can you have th primary key of one table appear twice in another table.
Is it possible to re-arrange primary key sequence?   (215 Views)
hi i am facing a problem with my application when someone deletes a record and hence its key disappears breaking the sequence e.g 1 - entry 2 - entry (3 - deleted no mere here) 4 - entry 5 - entry When key is not there my AJAX application fails to run properly due to not finding next id. Is there any method to re-arrange primary key values to a proper sequence when one or more numbers are missing so that i will write a query and whenever user deletes an item i will re-arrange the primary key sequence automatically....
exporting to mysql from access with primary keys intact?   (114 Views)
Is there any way to export my tables from access and keep the primary keys intact At the moment I use the DSN driver to link the tables and feed them into mysql but I lose the primary keys.