Database design help needed.

database design...

[bhavi]

Hi,
I'm in process of designing database to travel data.

I have a list of travel trips....
e.g.

Id Source Destination Date ....
1 s1 d1 10/11/08
2 s2 d2 ...
3 s3 d3 ...

For each travel trip i also have list of stops and no of stops differ between trips.
e.g.

Trip ID 1
=========
Date StopPlace ArrivalTime DepatureTime
10/11/08 place1 8.00AM 11.AM
11/11/08 place2 .. ....
12/11/08 place3 ..........

Trip ID 2
=========
.......
.......

Similarly for all trips i have a list of stop places.

I need help as how to the database design would be to handle these scenarios...

Thanks

[Habtom]

Welcome to WebmasterWorld, bhavi. [webmasterworld.com]

A little more explanation on how you want to implement it can bring a good amount of support from the people here.

[bhavi]

Thanks!

1. I have a list of trips (Name given for a travel from source to destination)
2. Each trip have a list of stops(this varies between stops)

I want to store these details in database.
How do i form the relation and what are all the tables needed?

To be simple. I want to store the following data in database.

1 t1 s1
2 t2 s2
3 t3 s3
. . .
. . .
. . .
n tn sn

for each row i have another table to be linked.
for t1
======

1 d1 e1
2 d2 e2
3 d3 e3
. . .
. . .
. . .
n dn en

for t2
======

1 j1 k1
2 j2 k2
3 j3 k3
. . .
. . .
. . .
n jn kn

and so on.......

hope this should be clear....

[Habtom]

I might have missed something, but you can create two tables:

Table_1
- id
- field_T
- field_S

Talbe_2
- id
- field_D
- field_E
- field_table_1_id

In the above case Table_2 extends the records you have in Table_1.

Habtom

[bhavi]

So, if have 1000 entries in table 1
ie. 1000 trips

I have to create 1000 tables (for table 2).
i.e. 1000 stop time table one for each trip?

[Habtom]

. . . to create 1000 tables

The way I see it, you don't need more tables.

When you say stop time, how many do you have for each trip?

[bhavi]

for each trip i have different number of stops.
say
for trip 1 - 4 stop times
for trip 2 - 6 stop times
for trip 3 - 5 stop times

stop time is a row.

e.g.
4 stop time refers to

1 stop_place1 time1
2 stop_place2 time2
3 stop_place3 time3
4 stop_place4 time4

[Habtom]

Yea, this is going to be a typical extended table structure.

You can have all your stop times stored in Table_B (Stop Times) and the reference id in Table_B refers to the id of Table_A (Trip).

I wonder if there could be any better solution.

[bhavi]

You mean to say.....

for each trip i have different number of stops.
say
for trip 1 - 4 stop times
for trip 2 - 6 stop times
for trip 3 - 5 stop times
stop time is a row.

Stop Time Table

stopid tripid stopplace time
1 1 stop_place1 time1
2 1 stop_place2 time2
3 1 stop_place3 time3
4 1 stop_place4 time4
5 2 stop_place5 time6
6 2 stop_place6 time6
7 2 stop_place7 time7
8 2 stop_place8 time8
9 2 stop_place9 time9
10 2 stop_place10 time10
11 3 stop_place11 time11
12 3 stop_place12 time12
13 3 stop_place13 time13
14 3 stop_place14 time14
15 3 stop_place15 time15

[Habtom]

Exactly.

[bhavi]

let me know if there is a better way to do.....
This seems to increase the number of queries i need to make to get the data back from database.
Not sure!
Is it?