Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5405
  • Last Modified:

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.
0
Itudk_2010
Asked:
Itudk_2010
3 Solutions
 
w00teCommented:
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
 
aelliso3Commented:
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 MatthewsCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now