Database design for a Car Rental web application

Hi all,

I am developing a Car Rental web application prototype. I have problems creating the database. By looking at some links I came up with the following tables:

Table Cars
(  CarID
TagNumber
Make
Model
CarYear
Category
mp3player
DVDPlayer
AirConditioner
Available
)

Table Customers
(
CustomerID
DrvLicNumber
FullName
Address
City
State
ZIPCode
Country

)

Table RentalRates
(
RentalRateID
Category
Daily
Weekly
Monthly
)

Table Booking
(
BookingID
CustomerID
CarID
MileageStart
MileageEnd
RentStartDate
RentEndDate
OrderStatus
)

What other information needs to be stored in the above tables and what other tables needs to be created to store car rental information?

Any ideas?
Looking forward to your reply.
Itudk_2010Asked:
Who is Participating?
 
w00teConnect With a Mentor Commented:
The only thing that I see missing is a table of damages which should have a one-to-many relationship with each car.  Cars typically have some faults (a scratch, cigarrette burn on the carpet, stain, whatever), and rental companies track that stuff so they can prove when a customer creates new damage so they can bill them for it.  

You also may want a table recording the locations the cars are held and map it as a foreign key relationship to the car.  Most rental companies allow you to rent from one store and return to another one in another state, and they typically charge you extra based on the difference in distance between the two stores (assuming you're not returning the car to the original source - like a one way trip thing).

Hope that helps!
-w00te
0
 
aelliso3Connect With a Mentor Commented:
Wow ... that's a really open ended question. There is a lot more things that could be added.
Below are a few things that I know will need to be included
>  In the Booking Table - RateAdjustment -- In case a customer price needs to be manually changed
>  New employee table with reference in the Booking table to show who rented the car out.
>  A way to show amount owed for any damages, or fuel surcharge
 
You can make it as big or small as you want. The best way to research what's needed is to look at other apps and see what they have available, then try to determine the structure from there.
 
0
 
Patrick MatthewsConnect With a Mentor Commented:
You might also move the customer address columns to a separate table.  Customers may have >1 address at any given time, and people/businesses also move from time to time.Also, you have columns on the Cars table for car features like:mp3playerDVDPlayerAirConditionerAt some point it might make sense to generalize features/optional equipment into its own table, or else you end up with 97 columns on your Cars table :)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Itudk_2010Author Commented:
Thank you all for your quick replies. Actually I want a simple database since this is a uinversity project and I would like to build a prototype web application based on the above database. I don't need to create the Employee table since the bookings are done through the website and I need to save the booking for each customer in the booking and customer tables. And I don't need to create the damages table as well. Could you guys suggest any more tables and field in the above database? I just need to create a simple database?

Thanks for your help.
0
 
GSGDBACommented:
Hi,
you missed drivers data. you can add a driver master table.
0
 
Itudk_2010Author Commented:
Still waiting for your reply on my previous psot?
0
 
Itudk_2010Author Commented:
Could you provide any example database?
0
 
Scott PletcherSenior DBACommented:
I echo aelliso3's first comments ... there's a lot that could/should be done here.

At min, you need to add state and VIN to the Cars table.  TagNumber is not unique w/o the state the tag is for.  VIN guarantees that you get back *exactly* the same car you sent out (i.e. not just any blue '97 Honda Accord, but *specifically* the one you rented out).

You probably won't bother with it, but technically you need to add a StartDate and EndDate to the RentalRates table, to allow fo specials, holiday pricing, etc..
0
 
w00teCommented:
Were not allowed to provide code for academic assignments but your current schema is pretty good. I'm not sure what you think is wrong with it.  Just type create table statements and add some data types/references and auto increment fields on the primary keys and your gold :)
0
 
Itudk_2010Author Commented:
Thank you all for your replies and suggestions. I will build the database with some changes which you all have provided.

0
 
Itudk_2010Author Commented:
ok
0
 
DhaestCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
All Courses

From novice to tech pro — start learning today.