SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Member
    Join Date
    May 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Design : single vs multiple tables

    I have to design a database with a structure lika

    Airlines -> Route -> Flights

    now each flight has a number of prices which are variable from day to day
    so there could be some price for say 15th May to 20th May..or the price could be specific to just one day..
    should i make a seperate table for prices for each flight..
    or a single common table..or mayb a table for each airline or each route..
    also should i have different tables for prices which are from say Day A to Day B and prices which are for a specific day...(since if i have a common table then there will be either a null or redundant data for the flight with a price on a specific date)

    Thanks for the help.

  2. #2
    SitePoint Member
    Join Date
    May 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i would also like to add that there would probably be 30 entries in the price table for each flight.

  3. #3
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, nikman!
    I would go with tables:
    Airlines
    id - Primary key
    name - Name of Airline

    Airports
    id - primary key
    name - name of Airport
    You will probably want also ICAO, FAA or IATA code

    Route
    id - Primary key
    airlineID - Airline id (Foreign key)
    origin - ID for origin airport (Foreign key)
    destination - ID destination airport (Foreign key)

    InterStops - intermediate stops
    id - Primary key
    RouteID - RouteID this stop belongs to (Foreign key)
    stopID - intermediate airports id (Foreign key)
    order - which stop starting from origin is this

    Flights
    id
    RouteID
    Departure datetime
    Arrival datetime
    Price - Or you need to make another table if (ant that is probably how it is) you want to have multiple prices (depending on class).

    Also depending on what you want, you have to make table available tickets, to be able to tell if flight is sold out or that particual price range is sold out.

  4. #4
    SitePoint Member
    Join Date
    May 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks Aleksejs..
    the problem with price however is that it could vary from day to day. for example say it could be higher on a sunday compared to other days. This might not make sense here but i might have to extend a similar design for hotels too.
    so say there are 30 rows for each flight in the price table with
    flightid
    from-date
    to-date
    price
    ..is that a good design or should i have different tables for each flight with the table-name identified by the flight-id and columns
    from-date
    to-date
    price

  5. #5
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, you definitely do not want to have table names dependant on flight id.
    I describe situation as I see it.
    You want to set base price for every particular flight depending on week day.
    If no other special price is defined, then this base price is considered to be the actual price.
    If, on other hand, you have defined that on particular date (range?) price should be equal to some other amount, then this price should be considered actual price.
    Is that what you want?

  6. #6
    SitePoint Member
    Join Date
    May 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there are a number of possible additions like special prices on national holidays and prices could also depend on seasons...
    so there could be upto say 30 entries on prices for any flight..

  7. #7
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well...
    A simple solution would be price table like:
    Prices
    id
    flightID
    valid_from
    valid_to
    priority

    And actual price is the one that has highest priority.
    So in MySQL that would look like:
    Code SQL:
    SELECT * FROM Prices
    WHERE valid_from <= $DATE AND
    valid_to >= $DATE AND
    flightID = $FLIGHT
    ORDER BY priority DESC LIMIT 1

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aleksejs, please be careful not to just hand people the answer to their homework assignments -- make them do the work

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

  9. #9
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Note taken
    It's just that in Latvian speaking forums majority is so unhelpful (and also those who ask for help fail to provide basic descriptions of problems) and when I came here - it struck me that everyone is so helpful and problems are interesting
    I am well aware that better is to teach how to catch a fish than just to give truckload of them.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    true

    besides, a truckload of fish will spoil before you can eat them all

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

  11. #11
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In those situations some tables get huge over time. You don't want this, and also you don't want to sort, search by date... You don't mention what platform you are using, but these are common problems for any.

    So a better option would be to limit the size of those tables in some way and provide a precalculated way to get the informations from it. For example you don't need to join all the tables each time somebody is asking for a price. 10 airlines*100 airports*10 flights*10prices*300days = 30 000 000 rows in the price table in one year (minimum). Not a good idea if you can easily avoid it.

  12. #12
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rosoft2001 View Post
    In those situations some tables get huge over time. You don't want this, and also you don't want to sort, search by date... You don't mention what platform you are using, but these are common problems for any.

    So a better option would be to limit the size of those tables in some way and provide a precalculated way to get the informations from it. For example you don't need to join all the tables each time somebody is asking for a price. 10 airlines*100 airports*10 flights*10prices*300days = 30 000 000 rows in the price table in one year (minimum). Not a good idea if you can easily avoid it.
    there's nothing wrong with huge tables. indexes are more than adequate to handle millions of rows.

    if you get too large for indexes to handle, you can use partitioning. i strongly advise against violation normalization rules for performance reasons when the native features of the database server provide all the performance you need.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  13. #13
    SitePoint Member
    Join Date
    Sep 2007
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    exactly:

    "Horizontal partitioning involves putting different rows into different tables. Perhaps customers with ZIP codes less than 50000 are stored in CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest."

    You just need to plan ahead, don't wait until the system is overloaded.

  14. #14
    SitePoint Member
    Join Date
    May 2008
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rosoft and alkesjs..
    my question probably wasnt frames right..but rosoft got it right..thanks a ton..

  15. #15
    secure webapps for all Aleksejs's Avatar
    Join Date
    Apr 2008
    Location
    Riga, Latvia
    Posts
    755
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are welcome!
    But can you please reiterate what exactly the problem was? And what is the solution that you gained from this conversation.
    It would really be nice


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
  •