Primary Keys: Single vs Composite

[Alex Aspiotis]

Objective:
I need help on deciding between using "Composite Primary Keys" (PK with multiple fields) versus a "Single Field Primary Key".

(I am not looking for advice on deciding between surrogate versus natural - I am going with surrogate, period.)

Assumptions:
1) I have attached table statements below - in summary the scenario deals with parent / child tables with multiple generations - assume 3 levels deep (e.g. child, parent, grandparent)

2) the database supports end users that deal with only "their own" records via a web application - this needs to scale to say 10K users simultaneously inserting / updating one record at a time

3) there are never batch feeds and rarely batch processing of records

The Alternatives:
Option 0) See table scripts below for account, order, order_detail and order_sub_detail. Every table has a single field primary key (foreign key field to its parent)

Option 1) See table scripts below for account, order_1, order_detail_1 and order_sub_detail_1. Primary key fields of parent table are inherited by the children with one extra ID field added to the end.

Option 0 Pros (Option 1 Cons):
1) Easy programming of joins - one field
2) ability to use auto_increment and InnoDB and no need to programmatically determine the next key
3) Easy to return last key inserted using Last_Insert_ID()


Option 1 Pros (Option 0 Cons):
1) Joins can skip tables if necessary - e.g. you can join a child to its grandparent directly without joining the parent
2) Potential performance increase (not sure about this) - to get to a record would it be faster to traverse the key in steps (akin to trunk, branch, leaf) e.g. order_id, order_detail_id, order_sub_detail_id as opposed to traversing a single ID field?

So my questions are:
1) Which method is faster - to pull the order_detail records for a given order_id, will it be faster to join the option 0 tables (order, order_detail) or the option 1 tables (order_1, order_detail_1)?
2) Can I get the best of both worlds by using MyISAM and its feature of allowing a composite key using auto_increment?
3) Are the pros / cons I listed valid? Are there more?
4) Can you recommend any books / web sites on this issue?

Thanks in advance!


Tables:
DROP TABLE IF EXISTS `sakila`.`tbl_account`;
CREATE TABLE `sakila`.`tbl_account` (
`Account_ID` int(10) unsigned NOT NULL auto_increment,
`First_Name` varchar(50) NOT NULL,
PRIMARY KEY (`Account_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `sakila`.`tbl_order`;
CREATE TABLE `sakila`.`tbl_order` (
`Order_ID` int(10) unsigned NOT NULL auto_increment,
`Account_ID` int(10) unsigned NOT NULL,
PRIMARY KEY (`Order_ID`),
KEY `FK_tbl_Order_tbl_Account` (`Account_ID`),
CONSTRAINT `FK_tbl_Order_tbl_Account` FOREIGN KEY (`Account_ID`) REFERENCES `tbl_account` (`Account_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `sakila`.`tbl_order_detail`;
CREATE TABLE `sakila`.`tbl_order_detail` (
`Detail_ID` int(10) unsigned NOT NULL auto_increment,
`Order_ID` int(10) unsigned NOT NULL,
PRIMARY KEY (`Detail_ID`),
KEY `FK_tbl_Order_Detail_tbl_Order` (`Order_ID`),
CONSTRAINT `FK_tbl_Order_Detail_tbl_Order` FOREIGN KEY (`Order_ID`) REFERENCES `tbl_order` (`Order_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `sakila`.`tbl_order_sub_detail`;
CREATE TABLE `sakila`.`tbl_order_sub_detail` (
`Sub_Detail_ID` int(10) unsigned NOT NULL auto_increment,
`Detail_ID` int(10) unsigned NOT NULL,
PRIMARY KEY (`Sub_Detail_ID`),
KEY `FK_tbl_Order_Sub_Detail_tbl_Order_Detail` (`Detail_ID`),
CONSTRAINT `FK_tbl_Order_Sub_Detail_tbl_Order_Detail` FOREIGN KEY (`Detail_ID`) REFERENCES `tbl_order_detail` (`Detail_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `sakila`.`tbl_order_1`;
CREATE TABLE `sakila`.`tbl_order_1` (
`Order_ID` int(10) unsigned NOT NULL,
`Account_ID` int(10) unsigned NOT NULL,
PRIMARY KEY (`Order_ID`),
KEY `FK_tbl_Order1_tbl_Account` (`Account_ID`),
CONSTRAINT `FK_tbl_Order1_tbl_Account` FOREIGN KEY (`Account_ID`) REFERENCES `tbl_account` (`Account_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `sakila`.`tbl_order_detail_1`;
CREATE TABLE `sakila`.`tbl_order_detail_1` (
`Order_ID` int(10) unsigned NOT NULL,
`Detail_ID` int(10) unsigned NOT NULL,
PRIMARY KEY (`Order_ID`,`Detail_ID`),
CONSTRAINT `FK_tbl_Order1_Detail1_tbl_Order_1` FOREIGN KEY (`Order_ID`) REFERENCES `tbl_order_1` (`Order_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `sakila`.`tbl_order_sub_detail_1`;
CREATE TABLE `sakila`.`tbl_order_sub_detail_1` (
`Order_ID` int(10) unsigned NOT NULL,
`Detail_ID` int(10) unsigned NOT NULL,
`Sub_Detail_ID` int(10) unsigned NOT NULL,
PRIMARY KEY (`Order_ID`,`Detail_ID`,`Sub_Detail_ID`),
CONSTRAINT `FK_tbl_Order_Sub_Detail1_tbl_Order_Detail_1` FOREIGN KEY (`Order_ID`, `Detail_ID`) REFERENCES `tbl_order_detail` (`Order_ID`, `Detail_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;