Aliaslaptop...Thanks for that response...NOW I understand what you were asking me to do.
Ok here it is.
CREATE TABLE `events` (
`eventid` int(10) NOT NULL AUTO_INCREMENT,
`event_date` date DEFAULT NULL,
`event_name` varchar(100) DEFAULT NULL,
`event_type` int(10) DEFAULT NULL,
`promotion` int(10) DEFAULT NULL,
`eventseries` int(7) DEFAULT NULL,
`session_id` int(10) DEFAULT NULL,
`attendance` decimal(3,0) DEFAULT NULL,
`venue_id` int(10) DEFAULT NULL,
PRIMARY KEY (`eventid`),
KEY `promotion` (`promotion`),
KEY `venue_id` (`venue_id`),
KEY `session_id` (`session_id`),
KEY `event_type` (`event_type`),
KEY `eventseries` (`eventseries`),
CONSTRAINT `events_ibfk_1` FOREIGN KEY (`promotion`) REFERENCES `promos` (`promo_id`),
CONSTRAINT `events_ibfk_2` FOREIGN KEY (`venue_id`) REFERENCES `venue` (`venue_id`),
CONSTRAINT `events_ibfk_3` FOREIGN KEY (`session_id`) REFERENCES `sessions` (`sessionid`),
CONSTRAINT `events_ibfk_4` FOREIGN KEY (`event_type`) REFERENCES `event_types` (`event_type_id`),
CONSTRAINT `events_ibfk_5` FOREIGN KEY (`eventseries`) REFERENCES `seriescode` (`seriesid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
Here is the info for the results table:
CREATE TABLE `results` (
`resultid` int(10) NOT NULL AUTO_INCREMENT,
`position` decimal(4,0) DEFAULT NULL,
`player_id` int(10) DEFAULT NULL,
`event_id` int(10) DEFAULT NULL,
`point_awarded` decimal(65,0) DEFAULT NULL,
PRIMARY KEY (`resultid`),
KEY `results_ibfk_1` (`event_id`),
CONSTRAINT `results_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`eventid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
Sample Insert - To Create an event
INSERT INTO `league_mgr`.`events` (`eventid`, `event_date`, `event_name`, `event_type`, `promotion`, `eventseries`, `session_id`, `attendance`, `venue_id`) VALUES (NULL, '2012-03-11', 'Tinley Bowl Weekly 03/11', '1', '1', '6', '1', '50', '1');
Insert Code for adding a new result
INSERT INTO `league_mgr`.`results` (
`resultid` ,
`position` ,
`player_id` ,
`event_id` ,
`point_awarded`
)
VALUES (
NULL , '2', '1', '2', RESULT OF CALCULATED QUERY
);