Solved

Database design for a Car Rental web application

Posted on 2010-09-14
13
4,129 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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
 

Author Comment

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

Expert Comment

by:Scott Pletcher
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This video teaches viewers about errors in exception handling.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…

770 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