Database schema

Osai22

Hi, I have a project that I want to work on, it's a vacation planning and booking website. Please I need help on the what table I should creat and their relationships. I am actually new on database and pup scripting. Please would need an urgent help, if possible, send me the complete vacation plan database scheme on a word document. Thank you

dagon

insufficient information to even guess.

Osai22

What information should I supply? Do I need to create the tables first and show it to you here, so that you can help me with mistakes?

bradgrafelman

Osai22 wrote:
Do I need to create the tables first and show it to you here, so that you can help me with mistakes?

That would at least be a starting point. Here's what you've told us so far:

"I've got this Foo project, and it's going to Bar and Baz. Please give me the details of DB setup and relationships so that this will work."

Notice how you have no idea where to even begin to understand what is needed? Neither do we. 🙂

dagon

if this was a work project i would be expecting a spec of at least 20 pages, i would then pass back a few hundred questions, have a day long meeting, and then maybe be ready to start.

Osai22

Ok, here are the tables, what I want to do exactly is to develop a website, where people can actually search for vacation packages based on an amount, duration and location. Once they click on search plan, it will display the available package based on their input on another page
with description of the hotel or rooms or the country and city which they intend to go, once the package is ok with the client, they can select the package and submit for the administrator to make the booking for them or they can make the booking themselves online by paying with their credit cards or atm.

I do not know if I can achieve these with what I have done here because I am so new with these database thing but will like to learn and become a guru in it, any assistance or help will be appreciated. thanks

Database: vacation_planning

[code]

-- Table structure for table `airlines`

CREATE TABLE IF NOT EXISTS airlines (
airline_id int(9) NOT NULL auto_increment,
airline_name varchar(50) NOT NULL,
country_id int(9) NOT NULL,
city_id int(9) NOT NULL,
PRIMARY KEY (airline_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `airlines`

-- Table structure for table `airports`

CREATE TABLE IF NOT EXISTS airports (
airprot_id int(9) NOT NULL auto_increment,
airport_name varchar(50) NOT NULL,
airport_city int(9) NOT NULL,
airport_country int(9) NOT NULL,
PRIMARY KEY (airprot_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `airports`

-- Table structure for table `city`

CREATE TABLE IF NOT EXISTS city (
city_id int(9) NOT NULL auto_increment,
cities varchar(50) NOT NULL,
country_id int(9) NOT NULL,
PRIMARY KEY (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `city`

-- Table structure for table `country`

CREATE TABLE IF NOT EXISTS country (
country_id int(9) NOT NULL auto_increment,
countries varchar(50) NOT NULL,
PRIMARY KEY (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `country`

-- Table structure for table `customers`

CREATE TABLE IF NOT EXISTS customers (
customer_id int(9) NOT NULL auto_increment,
customer_fname varchar(50) NOT NULL,
customer_lname varchar(50) NOT NULL,
customer_address varchar(250) NOT NULL,
customer_city int(9) NOT NULL,
customer_country int(9) NOT NULL,
customer_phone varchar(50) NOT NULL,
customer_email varchar(50) NOT NULL,
PRIMARY KEY (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `customers`

-- Table structure for table `flights`

CREATE TABLE IF NOT EXISTS flights (
flight_id int(9) NOT NULL auto_increment,
flight_no varchar(50) NOT NULL,
airline_name int(9) NOT NULL,
departure_city int(9) NOT NULL,
arrival_city int(9) NOT NULL,
departure_date timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
arrival_date timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (flight_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `flights`

-- Table structure for table `hotels`

CREATE TABLE IF NOT EXISTS hotels (
hotel_id int(9) NOT NULL auto_increment,
hotel_name varchar(50) NOT NULL,
hotel_address varchar(250) NOT NULL,
hotel_city int(9) NOT NULL,
hotel_country int(9) NOT NULL,
hotel_pictures blob NOT NULL,
hotel_description longtext NOT NULL,
PRIMARY KEY (hotel_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `hotels`

-- Table structure for table `hotel_rooms`

CREATE TABLE IF NOT EXISTS hotel_rooms (
hotel_room_id int(9) NOT NULL auto_increment,
hotel_name int(9) NOT NULL,
hotel_room_no varchar(50) NOT NULL,
room_type varchar(50) NOT NULL,
room_price varchar(50) NOT NULL,
bed_size varchar(50) NOT NULL,
PRIMARY KEY (hotel_room_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `hotel_rooms`

-- Table structure for table `price`

CREATE TABLE IF NOT EXISTS price (
price_id int(9) NOT NULL auto_increment,
planning_price varchar(250) NOT NULL,
PRIMARY KEY (price_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `price`

-- Table structure for table `vacation_booking`

CREATE TABLE IF NOT EXISTS vacation_booking (
vacation_booking_id int(9) NOT NULL auto_increment,
flight_id int(9) NOT NULL,
hotel_id int(9) NOT NULL,
room_id int(9) NOT NULL,
customer_id int(9) NOT NULL,
ref_booking varchar(50) NOT NULL,
PRIMARY KEY (vacation_booking_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `vacation_booking`

-- Table structure for table `vacation_planning`

CREATE TABLE IF NOT EXISTS vacation_planning (
vacation_planning_id int(9) NOT NULL auto_increment,
flight_id int(9) NOT NULL,
city_id int(9) NOT NULL,
price_id int(9) NOT NULL,
hotel_id int(9) NOT NULL,
room_id int(9) NOT NULL,
country_id int(9) NOT NULL,
PRIMARY KEY (vacation_planning_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;[/code]

bradgrafelman

Here are some preliminary comments on the DB schema:

Typo - airports.airprot_id should probably be airports.airportid ?
Why is city.cities named plural - "cities" instead of something like city_name? What do you plan on storing in this field?
Same as above for country.countries... what do you plan on storing here?
This comment applies for a couple of the tables (customers{?}, flights):

You've got some duplicate data; if you've got a "cities" table listing all of the cities, why don't these tables link back to a city in this table using its city id?

One exception might be the customers table; if you aren't going to have a table of every city in existence (sounds like a daunting task), then perhaps it's a good choice to leave that field as a VARCHAR.

However, I wouldn't think the same would be true for the 'country' values (it should be easy to obtain a list of all of the countries in existence, and how often does that list change?).
Why do you have the two date columns in the "flights" table set as TIMESTAMP columns? Furthermore, why on earth would you want a "default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP" attribute for a departure date? Common sense would suggest that this would never be useful, since you probably aren't adding/updating entries into this table on the exact moment that the flight departs.

As such, perhaps "DATETIME" would be a better choice for this field?
Isn't the "_name" suffix on the hotel_rooms.hotel_name column a bit misleading? What you're really doing is providing a way to cross-reference the room entry with an entire hotel entry, not just the hotel's name.

Same comments for flights.airline_name apply as well.
If you're planning on storing any sort of international data (e.g. non-English), you might want to consider switching to a Unicode character set (e.g. UTF-8). Note that if you do decide to make the switch (often recommended, especially for new projects), you'll want to do so globally - e.g. in the database as well as in your HTML (either in a <meta> tag, the HTTP headers, or better yet - both).
I'm no DBA expert, so I can't do justice to a comparison of the two engines, but have you had any exposure to the InnoDB storage engine? Specifically, using InnoDB allows you to utilize full text searching as well as foreign key relationships.

It's that last bit that I've used most recently. Essentially, you can setup the tables so that the "id" columns are explicitly linked to one another, e.g. flights.departure_city would have to be a value from the city.city_id column. It just forces a bit of referential integrity on the DB-side of things (as well as sort of self-documenting the relationships between the tables; you can simply look at the foreign keys to know how all of the tables relate to each other).

Osai22

Thank you bradgrafelman for your comment and help, like I said before I am just new on this and will need help to learn and go far in it, if you can help me further with this explanation and do some correction on the code I have written based on your comment, I believe it will help me further to understand what you have just explained and I have not had any exposure on InnoDB storage engine. Your further assistance on correcting the code and paste it here will be appreciated. Thank you so much again for your help.

Osai22

Hello brad, sorry to bother you again but I appreciate all the help you have given to me. This is what I have done again based on your previous comment.

-- Table structure for table `airlines`

CREATE TABLE IF NOT EXISTS airlines (
airlineid int(9) NOT NULL auto_increment,
airline_name varchar(50) NOT NULL,
countryid int(9) NOT NULL,
cityid int(9) NOT NULL,
PRIMARY KEY (airlineid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `airlines`

-- Table structure for table `airports`

CREATE TABLE IF NOT EXISTS airports (
airportid int(9) NOT NULL auto_increment,
airport_name varchar(50) NOT NULL,
airport_city int(9) NOT NULL,
airport_country int(9) NOT NULL,
PRIMARY KEY (airprotid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `airports`

-- Table structure for table `city`

CREATE TABLE IF NOT EXISTS city (
cityid int(9) NOT NULL auto_increment,
city_name varchar(50) NOT NULL,
countryid int(9) NOT NULL,
PRIMARY KEY (cityid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `city`

-- Table structure for table `country`

CREATE TABLE IF NOT EXISTS country (
countryid int(9) NOT NULL auto_increment,
country_name varchar(50) NOT NULL,
PRIMARY KEY (countryid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `country`

-- Table structure for table `customers`

CREATE TABLE IF NOT EXISTS customers (
customerid int(9) NOT NULL auto_increment,
customer_fname varchar(50) NOT NULL,
customer_lname varchar(50) NOT NULL,
customer_address varchar(250) NOT NULL,
customer_city int(9) NOT NULL,
customer_country int(9) NOT NULL,
customer_phone varchar(50) NOT NULL,
customer_email varchar(50) NOT NULL,
PRIMARY KEY (customerid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `customers`

-- Table structure for table `flights`

CREATE TABLE IF NOT EXISTS flights (
flightid int(9) NOT NULL auto_increment,
flight_no varchar(50) NOT NULL,
airline_name int(9) NOT NULL,
departure_city int(9) NOT NULL,
arrival_city int(9) NOT NULL,
departure_date DatTime NOT NULL default '0000-00-00 00:00:00',
arrival_date DateTime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (flightid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `flights`

-- Table structure for table `hotels`

CREATE TABLE IF NOT EXISTS hotels (
hotelid int(9) NOT NULL auto_increment,
hotel_name varchar(50) NOT NULL,
hotel_address varchar(250) NOT NULL,
hotel_city int(9) NOT NULL,
hotel_country int(9) NOT NULL,
hotel_pictures blob NOT NULL,
hotel_description longtext NOT NULL,
PRIMARY KEY (hotelid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `hotels`

-- Table structure for table `hotel_rooms`

CREATE TABLE IF NOT EXISTS hotel_rooms (
hotel_roomid int(9) NOT NULL auto_increment,
hotel_name int(9) NOT NULL,
hotel_room_no varchar(50) NOT NULL,
room_type varchar(50) NOT NULL,
room_price varchar(50) NOT NULL,
bed_size varchar(50) NOT NULL,
PRIMARY KEY (hotel_roomid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `hotel_rooms`

-- Table structure for table `price`

CREATE TABLE IF NOT EXISTS price (
priceid int(9) NOT NULL auto_increment,
planning_price varchar(250) NOT NULL,
PRIMARY KEY (priceid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `price`

-- Table structure for table `vacation_booking`

CREATE TABLE IF NOT EXISTS vacation_booking (
vacation_bookingid int(9) NOT NULL auto_increment,
flightid int(9) NOT NULL,
hotelid int(9) NOT NULL,
roomid int(9) NOT NULL,
customerid int(9) NOT NULL,
ref_booking varchar(50) NOT NULL,
PRIMARY KEY (vacation_bookingid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- Dumping data for table `vacation_booking`

-- Table structure for table `vacation_planning`

CREATE TABLE IF NOT EXISTS vacation_planning (
vacation_planningid int(9) NOT NULL auto_increment,
flightid int(9) NOT NULL,
cityid int(9) NOT NULL,
priceid int(9) NOT NULL,
hotelid int(9) NOT NULL,
roomid int(9) NOT NULL,
countryid int(9) NOT NULL,
PRIMARY KEY (vacation_planningid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

toma42

If this were my work project I'd kick my own ass for agreeing to such a poorly thought out proposal.

dagon;10971666 wrote:
if this was a work project i would be expecting a spec of at least 20 pages, i would then pass back a few hundred questions, have a day long meeting, and then maybe be ready to start.