Generalise a mySQL query using a Transaction & variables

[DJDaveMark]

Hi all,

I'm a Java Programmer who's taken on a website project by myself, therefore I've also had to design the database.

It will be a mySQL database. I have already designed it and successfully tested that the queries are getting out what was required. Although I'm not sure how portable my main INSERT statement will be if it is ever moved to another database. I'd like to hear your opinions on what I've come up with.

A simplified version of the database is as follows. A `race` can have many `stats`. A `race` has a `lineup` of one or more `person`s.

+-------+    +-----------+    +-----------+    +---------+
¦person ¦    ¦   lineup  ¦    ¦    race   ¦    ¦  stats  ¦
+-------+    +-----------+    +-----------¦    +---------+
¦  id   ¦   /¦     id    ¦\   ¦     id    ¦   /¦   id    ¦
¦ fname ¦----¦ person_id ¦----¦    time   ¦----¦ race_id ¦
¦ lname ¦   \¦  race_id  ¦/   +-----------+   \¦  m_stat ¦
+-------+    +-----------+                     ¦  t_stat ¦
                                               ¦  r_stat ¦
                                               +---------+

When I INSERT a new `race`, there must be `stats` for that race and it must have a `lineup` or the race shouldn't be inserted into the `race` table. So I use a transaction to make sure that if an exception is thrown by the database, it's all or nothing (and I'll inform the user it didn't work). Referential integrity is setup with ON DELETE CASCADE so that if a race is deleted then its lineup & stats are also removed.

Getting to the point...I am using the following SQL to INSERT a new race.

START TRANSACTION;
INSERT INTO `race` (
 `time`
)VALUES(
 100000
);

INSERT INTO `stats` (
 `race_id`, `m_stat`, `t_stat`, `r_stat`
)VALUES(
 @lastID:=LAST_INSERT_ID(), 1, 1.1, 14
),(
 @lastID, 2, 1.2, 14
),(
 @lastID, 3, 1.0, 13
),(
 @lastID, 4, 1.1, 14
);

INSERT INTO `lineup` (
 `race_id` , `person_id`
)VALUES(
 @lastID, 1
);
COMMIT;

Depending on exactly what kind of race it is, the stats and lineup tables could have any amount of rows added, not always four and one rows respectively as above. The Java Programming will build the query adding in the values which are hard coded above.

But that's pretty much it. Any comments? Any way to generalise it? Am I stuck with mySQL as the database?

 

[LKBrwnDBA]

The @lastID and LAST_INSERT_ID are non portable, but may have equivalent functions and/or can be emulated in SQL Server and Oracle.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb