Database Design

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

Database Design

Postby whitewater4562000 » Tue Feb 25, 2014 4:27 pm UTC

Hi,

I am modernising a program used at work to store booking information for travellers. The old program saved bookings into one large file which I am now trying to convert into a database. I have attached (hopefully) a copy of my database design in its current form. This make a lot of sense to me but am wondering if you can see any problems when it comes to implementing I'm hoping to avoid to many issues.

Basically one booking can contains a travel itinerary (flight, accommodation, transport etc), passenger info (who's traveling etc, can be 1 to ....), cost details and all or any transactions made and where they are traveling to (airport destination).

Any / all advice appreciated.
NOTE: Most fields entered with wrong data type / size. Just wanted a relationship model.
Attachments
database_design.png

Breakfast
Posts: 117
Joined: Tue Jun 16, 2009 7:34 pm UTC
Location: Coming to a table near you

Re: Database Design

Postby Breakfast » Tue Feb 25, 2014 5:03 pm UTC

Why aren't the airport_to and airport_from columns on itinerary_flights foreign keyed to Ids in airports? Why is customer_billing not linked to passenger_info?

User avatar
JBJ
Posts: 1263
Joined: Fri Dec 12, 2008 6:20 pm UTC
Location: a point or extent in space

Re: Database Design

Postby JBJ » Tue Feb 25, 2014 5:04 pm UTC

If you want to have only one unique booking_id, you'll want to make the booking_id the foreign key in the related tables, rather than having the primary keys of each related table the foreign key in the bookings table.
Because if a single booking can have multiple transactions, passengers, etc... then you'll need to repeat the same booking id in the bookings table (and lose the primary key constraint and uniqueness).

I'd put the booking_id as a foreign key in the tables: itinerary, airport, staff, customer_billing, passenger_info, costing, and transactions. Lose them as the foreign keys in the booking table.
And I'd do the same for the other sub tables, for itinerary_* into the main itinerary table and suppliers into costing.
So, you sacked the cocky khaki Kicky Sack sock plucker?
The second cocky khaki Kicky Sack sock plucker I've sacked since the sixth sitting sheet slitter got sick.

lgw
Posts: 437
Joined: Mon Apr 12, 2010 10:52 pm UTC

Re: Database Design

Postby lgw » Tue Feb 25, 2014 9:00 pm UTC

Looks like everything_name should be normalized. Reservation #s would be nice. Honestly, this looks more like homework than a real problem, to me. I'd expect 10x the messy details.
"In no set of physics laws do you get two cats." - doogly

Breakfast
Posts: 117
Joined: Tue Jun 16, 2009 7:34 pm UTC
Location: Coming to a table near you

Re: Database Design

Postby Breakfast » Tue Feb 25, 2014 9:59 pm UTC

Something else that occurred to me after me initial post. If you'll want to track address / phone / email outside of actual billings you'll want tables for each of those that would be tied to a passenger record. This way you could update [, say,] one phone record as opposed to every billing record for a particular passenger.

User avatar
whitewater4562000
Posts: 66
Joined: Sat Aug 23, 2008 4:22 pm UTC
Location: Scotland
Contact:

Re: Database Design

Postby whitewater4562000 » Wed Feb 26, 2014 10:07 am UTC

@JBJ That's what I was thinking once I read the relationships again.

It's been a long time 5+ years since I last looked at database(s)/design so pretty rusty.
I am trying to replicate the design of the original program UI so from how it works this is what I've managed to extract so far.
I'm sure there are better ways but people are reluctant to change.

Thanks for advice anyway


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 9 guests