SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Database Design

  1. #1
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Database Design

    Hi Experts,

    I am designing an application of bus reservation system.

    I am stuck with database design of the application. Hope you people can help me on this.

    Each bus have unique ID (say BUS NO), source & destination.

    Now let say for

    Bus B123 source is A and destination is X and in between it stops at C,Z,N,H,K also.
    Bus B456 source is C and destination is Y and in between it stops at Z,N,K,P,Q,R,T,I,D also.
    Bus B789 source is G and destination is S and in between it stops at N,H,K,D,M also.

    Now if user search for bus from
    C to H it should give B123
    N to K it should give B123, B456, B789
    K to D it should give B456, B789

    Again if he search for Bus from
    H to C it should not give B123 (i.e the bus goes only from C to H and not from H to C)

    The fact that need to consider here is the sequence.

    I can't keep all stations in sequence as admin may want to update the route(adding or deletion of station) in future.

    I hope you have understood what I am planning to design. Please help me with this design.

    I have to create this design in MySQL.

    Please do reply if you don't understand the requirement or have any doubt.

    Looking forward for your valuable comments and suggestions

    Regards,
    Vikrant

    Note : Station names can be different for simplicity I have used alpha bates. Stations don't follow any alphabetic order.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    create a table called something like bus_stops

    primary key will be bus number plus stop number, data will be stop name

    thus...

    B123 1 A
    B123 2 C
    B123 3 Z
    B123 4 N
    B123 5 H
    B123 6 K
    B123 7 X

    then to find a route that goes from C to H, do this --
    Code:
    SELECT stop_from.bus_no
         , stop_from.stop_no AS stop_from_no
         , stop_to.stop_no AS stop_to_no
      FROM bus_stops AS stop_from
    INNER
      JOIN bus_stops AS stop_to
        ON stop_from.bus_no = stop_to.bus_no
       AND stop_from.stop_no < stop_to.stop_no
     WHERE stop_from.stop_name = 'C'
       AND stop_to.stop_name = 'H'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    create a table called something like bus_stops

    primary key will be bus number plus stop number, data will be stop name

    thus...

    B123 1 A
    B123 2 C
    B123 3 Z
    B123 4 N
    B123 5 H
    B123 6 K
    B123 7 X

    then to find a route that goes from C to H, do this --
    Code:
    SELECT stop_from.bus_no
         , stop_from.stop_no AS stop_from_no
         , stop_to.stop_no AS stop_to_no
      FROM bus_stops AS stop_from
    INNER
      JOIN bus_stops AS stop_to
        ON stop_from.bus_no = stop_to.bus_no
       AND stop_from.stop_no < stop_to.stop_no
     WHERE stop_from.stop_name = 'C'
       AND stop_to.stop_name = 'H'
    can you let me know the fields in table

    I assume id(auto increment), bus_no, stop_no and stop_name are the only fields you are talking.

    When I run the query I am getting "Column 'bus_no' in field list is ambiguous"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vikrantmohite View Post
    can you let me know the fields in table

    I assume id(auto increment), bus_no, stop_no and stop_name are the only fields you are talking.
    yes, except ~not~ including the auto_increment id

    primary key will be bus number plus stop number, data will be stop name

    the ambiguous bus_no in the query was probably due to my initial reply, which i edited a few minutes afterwards to remove that error

    try it again please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, except ~not~ including the auto_increment id

    primary key will be bus number plus stop number, data will be stop name

    the ambiguous bus_no in the query was probably due to my initial reply, which i edited a few minutes afterwards to remove that error

    try it again please
    Query is working fine.
    But I just want to know what logic you have used to find the result. I am not good with queries. The stops in database may not be in order. In future if I want to change the route(add or delete the stops) the new entry will come in the last row.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by vikrantmohite View Post
    The stops in database may not be in order. In future if I want to change the route(add or delete the stops) the new entry will come in the last row.
    That's why you have the stop number column. That number indicates the order of the stops.
    So taking this data
    Code:
    B123 1 A
    B123 2 C
    B123 3 Z
    B123 4 N
    B123 5 H
    B123 6 K
    B123 7 X
    if in the future you want to add a new stop between the current stops C and Z, you'll have to update the stop numbers accordingly
    Code:
    B123 1 A
    B123 2 C
    B123 4 Z
    B123 5 N
    B123 6 H
    B123 7 K
    B123 8 X
    B123 3 P


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •