Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    6

    Unanswered: BD design problem

    I have a class project (flight reservation) where I need to create a DB in MSSQL server.
    so far I have an AirportTable ( City,Code,Name) and a FlightTable(FlightNumber,OriginCode,DestinationCode,DateTime) and a DistanceTable(originCode,destinationCode,Distance).
    At this point,I really have no Idea what the combination of FK would be and where would they be.
    (I was thinking to do the distance as an attribute in the AirportTable and treat like the Bill Of Material, But I can't think of how to do that either!
    Any help is very appreciated (alternative or improvement)
    Thank you,

  2. #2
    Join Date
    Sep 2011
    Posts
    6

    Found something

    Distance table - Dev Shed

    I have not tried this yet but it seems like this makes sense (This is the bill of material mapping BOM). I'll let you know when I get to actually create the MSSQL DB Tables.
    Last edited by jad0521; 09-22-11 at 20:47. Reason: Better

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    There is something weird with your FlightTable - the DateTime field (and also the FlightNumber PK) suggest me that you want to store there every flight. This is not 3FN and it will bring some problems.
    I think you should try something like:
    Airport(AirportCode, City, name)
    Direction(DirectionCode, OriginCode, DestinationCode, Distance)
    Flight(FlightCode, DirectionCode, FlightDateTime)

    PKs:
    Airport - AirportCode
    Direction - DirectionCode
    Flight - FlightCode

    FKs:
    Direction - OriginCode, DestinationCode - both to Airport table
    Flight - DirectionCode
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Sep 2011
    Posts
    6
    I think your solution is more sofisticated for sure. But if you look at my original tables, it is very close to yours if you see that you just added a simple PK that represents exactly my composit PK (OriginCode and DestCode). Do you agree with me?
    I'm no expert, in fact I just started doing this this semester. Any clarification will be very appreciated
    Thank you again

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Yes, you are right. I think it's a matter of taste, as I don't like the idea of composite PKs.
    But:
    - your design has a small problem, as it doesn't cover the situation where the same direction (let's say Iasi - Paris) can have a different distance, depending on stopover city (Bucharest or Viena)
    - my design has a small problem, as you need two records in the Direction table to cover both ways: Iasi - Viena - Paris and Paris - Viena - Iasi
    Hope that helps.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  6. #6
    Join Date
    Sep 2011
    Posts
    6

    Hello

    It turned out it is not that easy to do it really good! but since it's just a school project, my professor said I don't have to care about the distance, I can just put it in the flight table since it will be given and no need to calculate it or pull it out of a table.
    Much easier now, but I'm sure it is not the same way out there when the boss want it to be done

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by jad0521 View Post
    ...
    Much easier now, but I'm sure it is not the same way out there when the boss want it to be done
    Perhaps your boss will be different from mine. Mine just doesn't care about where I would store the Distance column. He would just say: "I gave you x people to work for you full time. You have time till ... to make it work. Success!"

    In such an environment, you can go two ways:
    - the quick and dirty path: model your database in such a way that it is a quick win and don't consider long term consequences. ... times passes ... You suddenly have to work for weeks till 9:00 pm, weekends included, to cope with some change.
    - the other way: model your database in a way that is conceptually sane. You may initially have to program a bit longer to shield the underlying complexity from the user. ... times passes ... You spend the weekends with your family, after having coped with some easy to handle changes during the weekdays.

    Many bosses really don't care how you solve it. But they will let you suffer the consequences of your bad decisions and let you reap the benefits from good decisions. And that's the kind of boss I like the most.

    On the other hand, you can also have a micro-manager as boss. It has been a while since we last saw a "I must change the value of this column to that value, but my boss doesn't allow me to use the UPDATE statement" on this forum. They will care where you store that Distance column. Most probably in the worst place. And at the end you will suffer from their mistakes.

    I don't strive to make my data models as good as I can to please my boss. I do it to make my life easier.
    When your data model is good, changes will be easy. "Easy" means "fast", "fast" means "happy users and management", "happy users and management" means a "good working relationship", a "good working relationship" means "working is and stays a joy". I enjoy working.
    I petty people who have to drag themselves to work day after day.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Sep 2011
    Posts
    6

    Hello

    Thank you Wim,
    If you look at my original post you will see that we have the same attitude. I was trying to make my data model as good as possible that's why I felt somthing has to be done with the distance field. I want to learn how to do a good data model now because I tend to be persistent in doing things the right way (I mean what seems best to ME) even if it is just a class project. But like you emphasized 'TIME PASSES' fast and my project is due tomorrow. I have to deliver something working. For a class project if it works a get good grade. For a real business project, if it works it's great, but I'd like to spend the weekends with my family I don't want to be expecting the call and work 16- 20 hours + weekends even though those kind of calls are expected no matter how good your DB is. The only difference is if the DB is good, less calls are expected
    Anyway, none learn everything at school, so I'm sure I'll stay on this forum a long time
    Thank again All of you,

  9. #9
    Join Date
    Sep 2011
    Posts
    75

    Try this

    Hello,

    Create the following structure

    AirportTable ( City,Code,Name) and a FlightTable(FlightNumber,OriginCode FK to city column(AirportTable ),DestinationCode FK to city column(AirportTable ),DateTime) and a DistanceTable(originCode FK to OriginCode(FlightTable) ,destinationCode FK to DestinationCode(FlightTable) ,Distance).



  10. #10
    Join Date
    Sep 2011
    Posts
    6

    AirportDB

    Hi jassi.singh,
    In your suggestion, what will then be the PK for the airportTable? is it the city AND the code? because only the 3 letters code is unique I think (you know like (JFK, PHL...)
    thank you
    Last edited by jad0521; 09-28-11 at 11:00. Reason: more details

Posting Permissions

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