Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    Exclamation Unanswered: Database design Hellppp pleeasssee

    Hello,
    I am designing a database for a small airline for schedules and bookings. In the booking process some of the customers are repeat customers with accounts and some of the customers are just one off customers never to be seen again. Each booking can have many individual people in it and each flight from A to B can have multiple stops say Via X, Y and Z. I thought I had it worked out but I have got muddled with it.

    My question is; As some of the people flying and booking are only going to fly once is it worth storing those names in the contacts table or should I just store them as a name with their contact info in the bookingDetails table. If I do store them in the contact table and they are not linked to a company or department how do I show this.

    Do I just create a join on the fly linking the contact to the booking via a contactid in the bookings detail table.

    I am under a lot of pressure to get this sorted out quickly any help much appreciated.



    Des (See attached file of relationships)
    Attached Thumbnails Attached Thumbnails relationships2 copy.jpg  
    the light is on, someone is home, but they dont know they are. HELLO!

  2. #2
    Join Date
    Nov 2003
    Posts
    11
    Yes I do think it is the right thing to do. In that way, you are only storing information of the people who fly regularly, or more than once. A separate table is the best way to tackle this.
    roxy

  3. #3
    Join Date
    Feb 2002
    Posts
    403
    Some thoughts:

    An airlines job is to turn everyone into frequent flyers

    Not all travellers are travelling on business

    Not all companies have departments (mine doesn't)

    No link is required to BookingDetail from contacts as this is implied via the bookings table, and could be queried if required.

    I see the model like:

    Dept-Comp-CompConJoin-Contact-BookingDetail-Booking

    From this I can query All contacts, all contacts working for companies, all contacts not working for companies who fly frequently, less frequently and so on.

    I would add an archive table for contacts that fly intermittently. And in my code search the contacts first, if no match then search the archive if no match then new entry.

    The archive table could be populated either quarterly by code or at a button push by the administrator. It is up to you.

    CompConJoin is a many to many join table that is that many of the conttacts will work for many of the companies. Some contacts will not work for any companies. A many join is therefore implied and required to facilitate this.

    HTH

  4. #4
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Originally posted by rukhsi
    Yes I do think it is the right thing to do. In that way, you are only storing information of the people who fly regularly, or more than once. A separate table is the best way to tackle this.

    Thanks rukhsi for the vote of confidence. But things are still niggling me and I don't know why yet.
    the light is on, someone is home, but they dont know they are. HELLO!

  5. #5
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Originally posted by dynamictiger
    Some thoughts:

    An airlines job is to turn everyone into frequent flyers

    Not all travellers are travelling on business

    Not all companies have departments (mine doesn't)

    No link is required to BookingDetail from contacts as this is implied via the bookings table, and could be queried if required.

    I see the model like:

    Dept-Comp-CompConJoin-Contact-BookingDetail-Booking

    From this I can query All contacts, all contacts working for companies, all contacts not working for companies who fly frequently, less frequently and so on.

    I would add an archive table for contacts that fly intermittently. And in my code search the contacts first, if no match then search the archive if no match then new entry.

    The archive table could be populated either quarterly by code or at a button push by the administrator. It is up to you.

    CompConJoin is a many to many join table that is that many of the conttacts will work for many of the companies. Some contacts will not work for any companies. A many join is therefore implied and required to facilitate this.

    HTH
    Thanks dynamictiger. I was going to have a database entry in the companies table as 'Not a Company' as default, and in the department table 'No Department' so all the people that are from companies with no department can leave the default value of 'No Department'. Then all the people with no company and no department are private individuals. All this said I am still confused about the booking details bit where - each journey can have many flights (stop on the way) and each booking detail can have many passengers. Its the passengers in the booking detail I dont know what to do with. If I reference the contacts through the company - department - contacts join then i can only have one contact per booking?? I have got myself realy confused with this! I am self taught so some jargon is lost on me but I must get this thing in shape over the next 24hrs or I'm horsemeat!!
    the light is on, someone is home, but they dont know they are. HELLO!

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Dap,

    Strategically because you may not have any idea of who the customer(contact) works for I'd make the contact the primary table and have a 1-m to the customer and that a 1-m to the dept table (related to dynamictiger's observations)... Then what you do in in the contact table you have columns denoting the FF miles, repeat customer, etc ... Or the one shot wonder. Setting up the relationship this way allows for the maximum info storage or the minimum (re: Business customers vs weekenders or one-ways) ... See what I'm talking about?

  7. #7
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Originally posted by M Owen
    Dap,

    Strategically because you may not have any idea of who the customer(contact) works for I'd make the contact the primary table and have a 1-m to the customer and that a 1-m to the dept table (related to dynamictiger's observations)... Then what you do in in the contact table you have columns denoting the FF miles, repeat customer, etc ... Or the one shot wonder. Setting up the relationship this way allows for the maximum info storage or the minimum (re: Business customers vs weekenders or one-ways) ... See what I'm talking about?
    It is a very small company that I am developing this for. There is no need for FF and the like. I am purely interested in being able to create a booking and if required save the user time and increase database integrity by pulling up the correct company when a contact for that company calls in to book. Maybe I should just have a contacts table as the main table and hang the company and depts table off of that. Actually I think that is what you are suggesting!! See my reply to dynamitiger about using many contacts in bookingdetails also.

    Thanks for taking the time to reply. Will be up for next 24hr working on this so its never too late to reply.

    Dapman
    the light is on, someone is home, but they dont know they are. HELLO!

  8. #8
    Join Date
    Feb 2002
    Posts
    403
    Given the 24 hour time constraint, the wisest thing to do is to zip your database and post it. Cumulatively we can fix it and you should have a solution that will work, albeit inelegantly in the time allowed. You should also clearly post your database plan so we (whoever we are) have an absolutely clear understanding of what the outcome is supposed to be.

  9. #9
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Originally posted by dynamictiger
    Given the 24 hour time constraint, the wisest thing to do is to zip your database and post it. Cumulatively we can fix it and you should have a solution that will work, albeit inelegantly in the time allowed. You should also clearly post your database plan so we (whoever we are) have an absolutely clear understanding of what the outcome is supposed to be.
    Hello again,
    I think I have almost cracked my problem. A lack of RDB knowledge here..... If I have more than one table that needs to refer to a single table like ContactTitleId in tblContacts links to tblTitle field called TitleID. and tblEmployees-EmployeeTitleID links to tblTitle-TitleID. Is this viable bearing in mind that I want to display both details, including title, of contacts and of employees on the same query at the same time?
    Last edited by dapman2002; 11-09-03 at 00:01.
    the light is on, someone is home, but they dont know they are. HELLO!

  10. #10
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Thanks think ive solved it
    Last edited by dapman2002; 11-09-03 at 17:52.
    the light is on, someone is home, but they dont know they are. HELLO!

Posting Permissions

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