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]

Re: Simple DB Design Questions

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]

Re: Simple DB Design Questions

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]

Re: Simple DB Design Questions

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]

Re: Simple DB Design Questions

On 9 Feb 2004 00:17:34 -0800, in mailing.databas e.mysql
michaelnewport@ yahoo.com (michael newport) wrote:
[color=blue]
>| 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)[/color]

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.
[color=blue]
>| 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.[/color]

This might be true but retrieving information/stats could be a real
nightmare.
[color=blue]
>| 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.[/color]

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

[Jeff North]

Re: Simple DB Design Questions

On 9 Feb 2004 00:17:34 -0800, in mailing.databas e.mysql
michaelnewport@ yahoo.com (michael newport) wrote:
[color=blue]
>| 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)[/color]

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.
[color=blue]
>| 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.[/color]

This might be true but retrieving information/stats could be a real
nightmare.
[color=blue]
>| 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.[/color]

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

[Jeff North]

Re: Simple DB Design Questions

On 9 Feb 2004 00:17:34 -0800, in mailing.databas e.mysql
michaelnewport@ yahoo.com (michael newport) wrote:
[color=blue]
>| 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)[/color]

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.
[color=blue]
>| 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.[/color]

This might be true but retrieving information/stats could be a real
nightmare.
[color=blue]
>| 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.[/color]

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

[419-Buster]

Re: Simple DB Design Questions


"Dimitris" <dterzis@yahoo. com> wrote in message
news:39fd8a9a.0 402071815.30abf 9d2@posting.goo gle.com...[color=blue]
> 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?[/color]


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]

Re: Simple DB Design Questions


"Dimitris" <dterzis@yahoo. com> wrote in message
news:39fd8a9a.0 402071815.30abf 9d2@posting.goo gle.com...[color=blue]
> 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?[/color]


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]

Re: Simple DB Design Questions


"Dimitris" <dterzis@yahoo. com> wrote in message
news:39fd8a9a.0 402071815.30abf 9d2@posting.goo gle.com...[color=blue]
> 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?[/color]


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.

[swdev1]

Re: Simple DB Design Questions

Hiya Dimitris.
I'm gonna assume you are evaluated on database design and coding skills for
this project.
If that's the case.
Use four tables, at a minimum, and normalize your data as much as possible.

There are real world reasons to keep things un-normalized, but this is for
an assignment, and a grade, and my hope is that your teacher is using the CJ
Date book on Intro to SQL as the text.
If not - you should get it - its still avail for sale/purchase.

mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"Dimitris" <dterzis@yahoo. com> wrote in message
news:39fd8a9a.0 402071815.30abf 9d2@posting.goo gle.com...[color=blue]
> 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. [snip][/color]

[swdev1]

Re: Simple DB Design Questions

Hiya Dimitris.
I'm gonna assume you are evaluated on database design and coding skills for
this project.
If that's the case.
Use four tables, at a minimum, and normalize your data as much as possible.

There are real world reasons to keep things un-normalized, but this is for
an assignment, and a grade, and my hope is that your teacher is using the CJ
Date book on Intro to SQL as the text.
If not - you should get it - its still avail for sale/purchase.

mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"Dimitris" <dterzis@yahoo. com> wrote in message
news:39fd8a9a.0 402071815.30abf 9d2@posting.goo gle.com...[color=blue]
> 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. [snip][/color]

[swdev1]

Re: Simple DB Design Questions

Hiya Dimitris.
I'm gonna assume you are evaluated on database design and coding skills for
this project.
If that's the case.
Use four tables, at a minimum, and normalize your data as much as possible.

There are real world reasons to keep things un-normalized, but this is for
an assignment, and a grade, and my hope is that your teacher is using the CJ
Date book on Intro to SQL as the text.
If not - you should get it - its still avail for sale/purchase.

mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"Dimitris" <dterzis@yahoo. com> wrote in message
news:39fd8a9a.0 402071815.30abf 9d2@posting.goo gle.com...[color=blue]
> 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. [snip][/color]

[michael newport]

Re: Simple DB Design Questions

what you need to have are opinions on the pros and cons of doing
things different ways, this way you can give reasoned arguments in
favour of a certain method, and reach a useful conclusion.

I have found that the things I learnt in an academic setting have
rarely been applied in real life.

This will of course be different for everyone which leads me back to
my first point.

[michael newport]

Re: Simple DB Design Questions

what you need to have are opinions on the pros and cons of doing
things different ways, this way you can give reasoned arguments in
favour of a certain method, and reach a useful conclusion.

I have found that the things I learnt in an academic setting have
rarely been applied in real life.

This will of course be different for everyone which leads me back to
my first point.