Solved

Database design for a Car Rental web application

Posted on 2010-09-14
13
3,986 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:Itudk_2010
13 Comments
 
LVL 12

Accepted Solution

by:
w00te earned 168 total points
ID: 33671842
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
 
LVL 11

Assisted Solution

by:aelliso3
aelliso3 earned 166 total points
ID: 33671860
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 166 total points
ID: 33671871
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
 

Author Comment

by:Itudk_2010
ID: 33672273
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
 
LVL 3

Expert Comment

by:GSGDBA
ID: 33672819
Hi,
you missed drivers data. you can add a driver master table.
0
 

Author Comment

by:Itudk_2010
ID: 33673484
Still waiting for your reply on my previous psot?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Itudk_2010
ID: 33673494
Could you provide any example database?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 33676955
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
 
LVL 12

Expert Comment

by:w00te
ID: 33677781
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
 

Author Comment

by:Itudk_2010
ID: 33686639
Thank you all for your replies and suggestions. I will build the database with some changes which you all have provided.

0
 

Author Comment

by:Itudk_2010
ID: 34029245
ok
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 34049785
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now