469,613 Members | 1,262 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

Simple DB Design Questions

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
Jul 19 '05 #1
30 1854
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
Jul 19 '05 #2
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
Jul 19 '05 #3
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
Jul 19 '05 #4
On 9 Feb 2004 00:17:34 -0800, in mailing.database.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****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 19 '05 #5
On 9 Feb 2004 00:17:34 -0800, in mailing.database.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****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 19 '05 #6
On 9 Feb 2004 00:17:34 -0800, in mailing.database.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****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 19 '05 #7

"Dimitris" <dt*****@yahoo.com> wrote in message
news:39**************************@posting.google.c om...
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...passenger, 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.
Jul 19 '05 #8

"Dimitris" <dt*****@yahoo.com> wrote in message
news:39**************************@posting.google.c om...
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...passenger, 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.
Jul 19 '05 #9

"Dimitris" <dt*****@yahoo.com> wrote in message
news:39**************************@posting.google.c om...
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...passenger, 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.
Jul 19 '05 #10
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" <dt*****@yahoo.com> wrote in message
news:39**************************@posting.google.c om...
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]

Jul 19 '05 #11
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" <dt*****@yahoo.com> wrote in message
news:39**************************@posting.google.c om...
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]

Jul 19 '05 #12
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" <dt*****@yahoo.com> wrote in message
news:39**************************@posting.google.c om...
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]

Jul 19 '05 #13
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.
Jul 19 '05 #14
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.
Jul 19 '05 #15
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.
Jul 19 '05 #16
Michael -
agreed -
but -
he's in an academic setting. Period. I teach as sql classes as well with
mySql utilizing the CJ Date Book.
He's not in real life [yet] so anything that will reduce his grade for the
assignment ? Who are we helping?

as to 'what you need to have are opinions ...' . Ah - I have many. Usually
paid for them, as well.
but that's another thing, and surely I digress.

If a project is for a grade in an academic setting, do it the way the
teacher wants it done. Period.
Anything else will be cause to 'take off points'.

But ? does it really matter ? Dimitris never posted back here asking further
questions.
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

"michael newport" <mi************@yahoo.com> wrote in message
news:63************************@posting.google.com ...
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.

Jul 19 '05 #17
Michael -
agreed -
but -
he's in an academic setting. Period. I teach as sql classes as well with
mySql utilizing the CJ Date Book.
He's not in real life [yet] so anything that will reduce his grade for the
assignment ? Who are we helping?

as to 'what you need to have are opinions ...' . Ah - I have many. Usually
paid for them, as well.
but that's another thing, and surely I digress.

If a project is for a grade in an academic setting, do it the way the
teacher wants it done. Period.
Anything else will be cause to 'take off points'.

But ? does it really matter ? Dimitris never posted back here asking further
questions.
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

"michael newport" <mi************@yahoo.com> wrote in message
news:63************************@posting.google.com ...
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.

Jul 19 '05 #18
Michael -
agreed -
but -
he's in an academic setting. Period. I teach as sql classes as well with
mySql utilizing the CJ Date Book.
He's not in real life [yet] so anything that will reduce his grade for the
assignment ? Who are we helping?

as to 'what you need to have are opinions ...' . Ah - I have many. Usually
paid for them, as well.
but that's another thing, and surely I digress.

If a project is for a grade in an academic setting, do it the way the
teacher wants it done. Period.
Anything else will be cause to 'take off points'.

But ? does it really matter ? Dimitris never posted back here asking further
questions.
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

"michael newport" <mi************@yahoo.com> wrote in message
news:63************************@posting.google.com ...
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.

Jul 19 '05 #19
> But ? does it really matter ? Dimitris never posted back here asking further
questions.
mondo regards [Bill]


Agreed, but it helps me pass the time of day, and who knows what
interesting people I will get to talk to !

....although to put this in context, I have just been on an Oracle
Designer training course and a lot of these issues came up with the
other group members.

Many hours of meetings then ensued over design issues.

I dont know how much Designer costs, but my conclusion was that
Designer is more of a burden. My experience mirrors this as I
previously survived without a design tool for 13 years.
Jul 19 '05 #20
> But ? does it really matter ? Dimitris never posted back here asking further
questions.
mondo regards [Bill]


Agreed, but it helps me pass the time of day, and who knows what
interesting people I will get to talk to !

....although to put this in context, I have just been on an Oracle
Designer training course and a lot of these issues came up with the
other group members.

Many hours of meetings then ensued over design issues.

I dont know how much Designer costs, but my conclusion was that
Designer is more of a burden. My experience mirrors this as I
previously survived without a design tool for 13 years.
Jul 19 '05 #21
> But ? does it really matter ? Dimitris never posted back here asking further
questions.
mondo regards [Bill]


Agreed, but it helps me pass the time of day, and who knows what
interesting people I will get to talk to !

....although to put this in context, I have just been on an Oracle
Designer training course and a lot of these issues came up with the
other group members.

Many hours of meetings then ensued over design issues.

I dont know how much Designer costs, but my conclusion was that
Designer is more of a burden. My experience mirrors this as I
previously survived without a design tool for 13 years.
Jul 19 '05 #22
Designer Training Course ?
hmmm - did you join OTN ? [get your free stuff ? ]
I will usually do ONE to THREE ERD's for a client as part of a deliverable
for sign off .
Some are HUGE .
I don't use Designer for ERD's, of course.

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

"michael newport" <mi************@yahoo.com> wrote in message
news:63************************@posting.google.com ...
But ? does it really matter ? Dimitris never posted back here asking further questions.
mondo regards [Bill]


Agreed, but it helps me pass the time of day, and who knows what
interesting people I will get to talk to !

...although to put this in context, I have just been on an Oracle
Designer training course and a lot of these issues came up with the
other group members.

Many hours of meetings then ensued over design issues.

I dont know how much Designer costs, but my conclusion was that
Designer is more of a burden. My experience mirrors this as I
previously survived without a design tool for 13 years.

Jul 19 '05 #23
Designer Training Course ?
hmmm - did you join OTN ? [get your free stuff ? ]
I will usually do ONE to THREE ERD's for a client as part of a deliverable
for sign off .
Some are HUGE .
I don't use Designer for ERD's, of course.

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

"michael newport" <mi************@yahoo.com> wrote in message
news:63************************@posting.google.com ...
But ? does it really matter ? Dimitris never posted back here asking further questions.
mondo regards [Bill]


Agreed, but it helps me pass the time of day, and who knows what
interesting people I will get to talk to !

...although to put this in context, I have just been on an Oracle
Designer training course and a lot of these issues came up with the
other group members.

Many hours of meetings then ensued over design issues.

I dont know how much Designer costs, but my conclusion was that
Designer is more of a burden. My experience mirrors this as I
previously survived without a design tool for 13 years.

Jul 19 '05 #24
Designer Training Course ?
hmmm - did you join OTN ? [get your free stuff ? ]
I will usually do ONE to THREE ERD's for a client as part of a deliverable
for sign off .
Some are HUGE .
I don't use Designer for ERD's, of course.

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

"michael newport" <mi************@yahoo.com> wrote in message
news:63************************@posting.google.com ...
But ? does it really matter ? Dimitris never posted back here asking further questions.
mondo regards [Bill]


Agreed, but it helps me pass the time of day, and who knows what
interesting people I will get to talk to !

...although to put this in context, I have just been on an Oracle
Designer training course and a lot of these issues came up with the
other group members.

Many hours of meetings then ensued over design issues.

I dont know how much Designer costs, but my conclusion was that
Designer is more of a burden. My experience mirrors this as I
previously survived without a design tool for 13 years.

Jul 19 '05 #25
yep joined otn, now focusing on forms and pl/sql

how do you write your erd's ?
Jul 19 '05 #26
yep joined otn, now focusing on forms and pl/sql

how do you write your erd's ?
Jul 19 '05 #27
yep joined otn, now focusing on forms and pl/sql

how do you write your erd's ?
Jul 19 '05 #28
Dear all...

Thanks very much for the invaluable advice! :-)

Cheers,

Dimitris
Jul 19 '05 #29
Dear all...

Thanks very much for the invaluable advice! :-)

Cheers,

Dimitris
Jul 19 '05 #30
Dear all...

Thanks very much for the invaluable advice! :-)

Cheers,

Dimitris
Jul 19 '05 #31

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Patchwork | last post: by
6 posts views Thread by Jacek Dziedzic | last post: by
17 posts views Thread by Chris M. Thomasson | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.