SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please Help me design this MySql tables.

    Hello there..Im new to PHP and MySQL, but im going on..
    However, I have home work please Do find for me a solution ((

    An Airline Database, Crew_Info table has been created with all crew indivisual info, (Staff number) is the key.
    duty_master table has been created including all duties (Stadby duties)
    Duty_code is the key.
    flight_master table has been created including all flights number,
    sectors and flight information (Flight number is the key)
    I got the job done of comunicating between PHP and these tables
    data IN and OUT..update, insert, display..

    The problem now comes when I want to roster one indivisual crew..
    for example:
    I want this staff number 10 to be assigned to flight A11
    and staff 22 to be assigned to flight A11
    and so on...
    flight should be assigned to any date that user choose.

    For example..
    in day 01-01-2009
    chicago-jfk-chicago flight number A11 (and i can see the crew in this flight)
    lasvegas-jfk-lasvegas....flight number B12 (and i can see the crew in this flight)
    So i can list all flights in day 01-01-2009 and I can navigate the assigned crew to this flight...
    if i choose flight A11 on day 01-01-2009 i should see the crew
    and on day 02-02-2009 same flight diffrent crew
    --- short words.. The flights in this particilar day and the crew in particilar flight in particilar day..

    I cannot figure it out..
    If i make the date as primary..they hell of information will be in just one day one row....imagine 60 flights and many duties...etc..
    and if i make the flight number the key then cannot duplicate the flight..
    Note..that a uniqe staff number cannot be duplicate in a certain day
    and a uniqe flight number cannot be duplicate in a certain day..
    because one staff number (one crew) will be assigned one duty in one day.
    How to desgin this part

    SOS!

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please show us the create table statements so we can see more clearly waht your structure is like.

    bazz

  3. #3
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear, Thanks for your reply, When I get home I will paste here the tables i have created, But Im talking about how to create a new table.
    Becuase the current tables I have is
    1.crew_info (staf_no ..f_name, l_name..etc..)
    2.Master_flights (flight_number, sector1, sector2, sector3..time of flight..etc)
    3.Master_duty (stndby_code, stndby_descr..etc..)

    So it has nothing to do with what I want to do..

    Now the next step is..
    How to create a table..that holds a specific date and inside this date
    all the flights assigned to this date, and crew assigned to this flights.
    for example date 01.01.2009 has flight1, flight2, flight3, flight4
    and crew assigned to flight1 is abc, def, ghi and crew assigned to flights2 is ..xxx..ccc..bbb.... so on...
    take a note that staff number (crew) should not be duplicate on the same date...

    Thank you again.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    dear deadline

    we will not do your homework for you

    do you have a specific question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The clue is "many to many", so solve that (or them )

  6. #6
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello..

    When I wrote Home Work I didnot mean School Home work
    I ment my project to work in!
    Im a system Admin for a company and they require such a project
    I need to finish within a dead line time, Im not a school student :/

    So your help in this matter will make my boss decides about my future!!

    The specific question is!
    Shall I make an indexed colum with dates, and colum with staff numbers
    NO Primary key, and insert all data in one table?

    PS: Sorry if ididnot get your clue, My primary job with servers, deploying
    and writing drivers..etc..under unix, MySql projects is still new to me..

    Thanks alot for your attention.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, maybe it's not a school homework question -- it sounds a lot more like a boss homework question

    there's no way any manager would assign a project of this complexity (airline flights, flight crews, etc.) to a non-database person unless it was as a homework assignment to test your competency

    if this is a real project for a real airline, perhaps you might mention which one, so as we can avoid flying on it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, number one..bosses and managers usually doesnot understand what even the word "Operating system" means..When i spent days to create the staff database..with all the options..update, create, edit..and the html desgin..legality under php..plus some messages in java script..
    He told me.."You spent weeks for this only" !!

    R937, I would suggest that no need to under estimate people, companies,
    or whoever in this world..
    My Airline company has bought a system from a sweidsh company with excatly 3 millions USdollars.
    and the system has total failure, bad display, and the 10 people who signed
    the contract for this system has been busted and terminated.

    So I offered to create a simple yet easy system..
    as long as I maintaine the overall Emailing systems, and servers..

    However,
    Seems this Thread start to take a diffrent way, non profffesional
    way. although all i needed is database design & hints.

    PS: If you see what I saw on major international airlines..out of my position
    then I would suggest you start to use your own car and avoid most airlines.

    Thanks alot.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    so with very little database experience, you've been selected to re-engineer a 3-million-dollar database system that failed?

    i would suggest you get your résumé ready, because yours will probably fail too

    (and your boss should be fired)

    sorry, but that's how i see it

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

  10. #10
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "Fail" ? What does this word means?
    I will get back to you by end of this month, When system is up and ready in action.
    Thanks for your help and moral support.


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
  •