Simple DB Design Questions

[Dimitris]

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

[michael newport]

why not try both ?

try 1 table first, then try 4 tables ....

the problem with 1 table is that you will have repeating fields,
(something that DB purists will have a problem with)
this will waste some disk space, but you will not need to do any joins
to retrieve data, so it should be faster to run and faster to create.

the problem with 4 tables is that your design becomes more
complicated,
meaning maintenance is more complex, perhaps you will have 4 screens
instead of 1.

Regards
Michael Newport

[michael newport]

why not try both ?

try 1 table first, then try 4 tables ....

the problem with 1 table is that you will have repeating fields,
(something that DB purists will have a problem with)
this will waste some disk space, but you will not need to do any joins
to retrieve data, so it should be faster to run and faster to create.

the problem with 4 tables is that your design becomes more
complicated,
meaning maintenance is more complex, perhaps you will have 4 screens
instead of 1.

Regards
Michael Newport

[michael newport]

why not try both ?

try 1 table first, then try 4 tables ....

the problem with 1 table is that you will have repeating fields,
(something that DB purists will have a problem with)
this will waste some disk space, but you will not need to do any joins
to retrieve data, so it should be faster to run and faster to create.

the problem with 4 tables is that your design becomes more
complicated,
meaning maintenance is more complex, perhaps you will have 4 screens
instead of 1.

Regards
Michael Newport

[Jeff North]

On 9 Feb 2004 00:17:34 -0800, in mailing.databas e.mysql
mi************@ yahoo.com (michael newport) wrote:

| why not try both ?
|
| try 1 table first, then try 4 tables ....
|
| the problem with 1 table is that you will have repeating fields,
| (something that DB purists will have a problem with)
Not really, some tables/databases are best left un-normalised.
The main problem with only using a single table is the accuracy of the
repeating data. For example, in the departure section of data some
people may put Los Angeles and other people might enter LAX or L.A. or
LA. Imagine doing a query to find the number of departures from any
given location.
| this will waste some disk space, but you will not need to do any joins
| to retrieve data, so it should be faster to run and faster to create.
This might be true but retrieving information/stats could be a real
nightmare.
| the problem with 4 tables is that your design becomes more
| complicated,
| meaning maintenance is more complex, perhaps you will have 4 screens
| instead of 1.

So? :-)
What about all the reports/stats information pages? :-)
---------------------------------------------------------------
jn****@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Jeff North]

On 9 Feb 2004 00:17:34 -0800, in mailing.databas e.mysql
mi************@ yahoo.com (michael newport) wrote:

| why not try both ?
|
| try 1 table first, then try 4 tables ....
|
| the problem with 1 table is that you will have repeating fields,
| (something that DB purists will have a problem with)
Not really, some tables/databases are best left un-normalised.
The main problem with only using a single table is the accuracy of the
repeating data. For example, in the departure section of data some
people may put Los Angeles and other people might enter LAX or L.A. or
LA. Imagine doing a query to find the number of departures from any
given location.
| this will waste some disk space, but you will not need to do any joins
| to retrieve data, so it should be faster to run and faster to create.
This might be true but retrieving information/stats could be a real
nightmare.
| the problem with 4 tables is that your design becomes more
| complicated,
| meaning maintenance is more complex, perhaps you will have 4 screens
| instead of 1.

So? :-)
What about all the reports/stats information pages? :-)
---------------------------------------------------------------
jn****@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[Jeff North]

On 9 Feb 2004 00:17:34 -0800, in mailing.databas e.mysql
mi************@ yahoo.com (michael newport) wrote:

| why not try both ?
|
| try 1 table first, then try 4 tables ....
|
| the problem with 1 table is that you will have repeating fields,
| (something that DB purists will have a problem with)
Not really, some tables/databases are best left un-normalised.
The main problem with only using a single table is the accuracy of the
repeating data. For example, in the departure section of data some
people may put Los Angeles and other people might enter LAX or L.A. or
LA. Imagine doing a query to find the number of departures from any
given location.
| this will waste some disk space, but you will not need to do any joins
| to retrieve data, so it should be faster to run and faster to create.
This might be true but retrieving information/stats could be a real
nightmare.
| the problem with 4 tables is that your design becomes more
| complicated,
| meaning maintenance is more complex, perhaps you will have 4 screens
| instead of 1.

So? :-)
What about all the reports/stats information pages? :-)
---------------------------------------------------------------
jn****@yourpant sbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------

[419-Buster]

"Dimitris" <dt*****@yahoo. com> wrote in message
news:39******** *************** ***@posting.goo gle.com...

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)

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?

You need to look at what the primary key is going to be. Is this a
passenger-based system or a booking-based system. The reason I mention this
is because you could get away with one table, if you are booking-based. That
is, each row is a different booking item. IMO, it is still bad design to do
one table, because of the lack of expandibility. My opinion is to create
three tables...passen ger, trip, control. Joining them is really easy. For
example, for a booking-based system, I'd go with...

PASSENGER
-rowid
-name
-...

TRIP
-rowid
-passengerid
-controlid
-departer...

CONTROL
-rowid
-operatorname
You certianly wouldn't have any issues with record collision in this
scenereo. I use this type of structure for many of my db apps, and I have
thousands of transactions/minute and >15M rows in many systems.

[419-Buster]

"Dimitris" <dt*****@yahoo. com> wrote in message
news:39******** *************** ***@posting.goo gle.com...

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)

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?

You need to look at what the primary key is going to be. Is this a
passenger-based system or a booking-based system. The reason I mention this
is because you could get away with one table, if you are booking-based. That
is, each row is a different booking item. IMO, it is still bad design to do
one table, because of the lack of expandibility. My opinion is to create
three tables...passen ger, trip, control. Joining them is really easy. For
example, for a booking-based system, I'd go with...

PASSENGER
-rowid
-name
-...

TRIP
-rowid
-passengerid
-controlid
-departer...

CONTROL
-rowid
-operatorname
You certianly wouldn't have any issues with record collision in this
scenereo. I use this type of structure for many of my db apps, and I have
thousands of transactions/minute and >15M rows in many systems.

[419-Buster]

"Dimitris" <dt*****@yahoo. com> wrote in message
news:39******** *************** ***@posting.goo gle.com...

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)

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?

You need to look at what the primary key is going to be. Is this a
passenger-based system or a booking-based system. The reason I mention this
is because you could get away with one table, if you are booking-based. That
is, each row is a different booking item. IMO, it is still bad design to do
one table, because of the lack of expandibility. My opinion is to create
three tables...passen ger, trip, control. Joining them is really easy. For
example, for a booking-based system, I'd go with...

PASSENGER
-rowid
-name
-...

TRIP
-rowid
-passengerid
-controlid
-departer...

CONTROL
-rowid
-operatorname
You certianly wouldn't have any issues with record collision in this
scenereo. I use this type of structure for many of my db apps, and I have
thousands of transactions/minute and >15M rows in many systems.