Member Avatar for imanewbie015

hi,

im having a problem on my project dont know how to normalize the customer information and the flight details

  • 5 Contributors
  • 14 Replies
  • 185 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by richieking

Dani AI

Generated

As pointed out, separate data that is intrinsic to an entity from transactional data. Treat “flight” as two concepts: a FlightDefinition (the reusable route/flight number) and a ScheduledFlight (a specific date/time/aircraft). The six-table start from is on the right track, but splitting those layers and adding a small set of link tables will remove most normalization and relationship confusion.

Suggested core tables (keys and purpose only):

  • Airports: AirportCode (IATA) PK, name, city, timezone.
  • Aircraft: AircraftID (or TailNumber) PK, model, seatmap reference.
  • FlightDefinition: FlightDefID PK, FlightNumber, OriginAirport, DestAirport.
  • ScheduledFlight: ScheduledFlightID PK, FlightDefID FK, DepartureUTC, ArrivalUTC, AircraftID FK, status.
  • Customers: CustomerID PK, name, contacts, passport/ID, FF number.
  • Reservations: ReservationID PK, CustomerID FK, BookingDate, total.
  • ReservationSegment (or Ticket): SegmentID PK, ReservationID FK, ScheduledFlightID FK, SeatNumber, FareClass, TicketNo.
  • Seats (optional): SeatID PK, AircraftModel, row/seat, cabin.
  • Payments/Audit tables for transactions and history.

Key relationship notes:

  • Customers 1..N Reservations. Reservations 1..N ReservationSegments. FlightDefinition 1..N ScheduledFlights.
  • ReservationSegments implement the many-to-many between reservations and scheduled flights (a booking can include multiple legs; a scheduled flight has many bookings).
  • Do not use FlightNumber as a PK (it repeats). Store times in ScheduledFlight (use UTC) and keep locale/timezone metadata on airports.
  • Enforce seat uniqueness per scheduled flight with a unique constraint on (ScheduledFlightID, SeatNumber) to avoid double-booking; use transactions or DB constraints rather than application-only checks.

Minimal example DDL snippet (illustrative):

CREATE TABLE FlightDefinition (FlightDefID INT PRIMARY KEY, FlightNumber VARCHAR(10), OriginAirport CHAR(3), DestAirport CHAR(3));
CREATE TABLE ScheduledFlight (ScheduledFlightID INT PRIMARY KEY, FlightDefID INT REFERENCES FlightDefinition(FlightDefID), DepartureUTC DATETIME, ArrivalUTC DATETIME, AircraftID INT);
CREATE TABLE ReservationSegment (SegmentID INT PRIMARY KEY, ReservationID INT, ScheduledFlightID INT REFERENCES ScheduledFlight(ScheduledFlightID), SeatNumber VARCHAR(4), UNIQUE (ScheduledFlightID, SeatNumber));

Practical tips: prototype in Access if required, but consider MySQL/PostgreSQL (or SQLite for single-user) for concurrency and FK support as suggested. Populate the schema with sample data and step through a booking flow to validate foreign keys, seat allocation, and pricing logic.

Recommended Answers

All 14 Replies

Member Avatar for Ezzaral

Separate them based on what data is completely intrinsic to each. What information about the customer applies to them whether they are on or off of a plane? Similarly, what data about the flight would apply even if there were no passengers on it?

Member Avatar for imanewbie015

thnk you sir...

Member Avatar for imanewbie015

sorry sir ... i have tried connecting the attributes but i find it very hard..

here what I've started so far...

I have 6 tables for my airline reservation system..(But I dont know if these are enough)

1. Airports
2. Aircrafts details
3. Customer information
4. Booking Details
5. Reservation
6. Flight Details

actually its a total mess...hehehe
if u can give me diagram or a much better relationship... it would be a great help...thanks in advance...sorry again if i marked this post too soon...

Member Avatar for imanewbie015

hi sir,

please help to complete my system im having a problem in connecting my table attributes..
i am a student and this is my project..
this is the link of my access database about the system..

if you see some area of opportunity to enhance my design i would happy to apply it...
thanks in advance....

Member Avatar for TrustyTony

What Python are you using or are you another of those lost lambs?

Member Avatar for richieking

easy tonyjv easy tonyjv .

Member Avatar for TrustyTony

Maybe we could persuade him to change from Access to sqlite or MySQL? ;)

Member Avatar for imanewbie015

sorry sir... i was looking for database but it led me into python...hehehe.. like i said im new...next time ill put it on right category...

Member Avatar for peter_budo

sorry sir... i was looking for database but it led me into python...hehehe.. like i said im new...next time ill put it on right category...

That is as lame answer as it ever gets, since you already had existing post back 4 days in Database Design section. You just didn't cared where you drop you homework as long you get answer to it.

Thread moved from Python to DB design
Threads merged

Member Avatar for imanewbie015

I thought I would get some help.. but what did I get.... :(

Member Avatar for peter_budo

You never showed any progress in work and was just waiting for others to do your work. That is not how it is working on this forum. So no point moaning about it week later

Member Avatar for richieking

Right on buddy. Right on!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.