Hi folks...
As part of an assignment, I have to design and implement a fairly
small MYSQL 4.0.17 database for a fictitious travel agency. This
database will store data from customers submitting it through
application forms. I will test my implementation on an old Linux box
(RedHat 6.x with PHP 4.0.5 and MySQL 4.0.17, running on an AMD
processor and 64MB of memory).
Each APPLICATION form will have 4 parts:
- PASSENGER details: name, surname, etc.
- CONTACT details: phone number(s), postal and e-mail addresses
- INFORMATION on the trip: departure and return dates, etc.
- CONTROL data: agency-specific (e.g., booking operator)
If I put all the above information into 1 table, APPLICATION, it will
have around 30 fields and a record size in the order of 1 KB/customer
(no more than 10,000 records expected).
If I put the information into 4 tables (PASSENGER, CONTACT,
INFORMATION, CONTROL), then none will have more than 10 fields/record,
but I need to associate them with, say, the PASSENGER key. But, if I
use auto increment, I will have to rely on the mysql_insert_id()
function, which I am not happy about, since multiple simultaneous
accesses of the database is in the specs of the assignment and this
function examines the last INSERT. (Table locks and transaction
processing might help here.)
So, my question is, which design path to follow: 1 table, or 4 tables
and, if the latter, how do I go about reliably retrieving the
auto_increment key?
Cheers,
Dimitris