Christina Christina - 1 month ago 19
MySQL Question

How to normalize my MySQL Database

I have PHP/MYSQL car rental site. In the MYSQL table i store


  1. car license plates

  2. car specs (like AC, brand and such)

  3. price per day (30 colums), since price for 1 day is X euro per day, and for 30 days let's say is Y euro per day

  4. insurance per day (this is a per
    car thing because it depends on the specific car history, year,
    brand, model and such). So since there are 30 days in a month, we
    have here another 30 columns, since insurance for 1 day <> insurance
    for 28 days let's say



Now if i put all this stuff in I will have about 70 colums.
Any smarter way of doing it to avoid a performance blow?
I do not control the prices and there is not a daily price or daily insurance formula.

One ideea would be to use the car plates as an index and blow it in 2 tables, one with prices (35 rows), one with insurance (35 rows). Any other?

The DB has 1000 cars or so. I get about 10.000 queries a day in the DB

Kind thanks.

Answer

A quick an dirty attempt below. I'd move prices and insurence costs in dedicated tables, each having a car_id and days field.

Select brand,type,ac,seats FROM cars
LEFT JOIN prices ON cars.id = prices.car_id
LEFT JOIN insurence_costs ON cars.id = insurence_costs.car_id
WHERE
    licensePlate = 'HH-OH-234'
    AND prices.days = 28
    AND insurence_costs.days = 28

Update: Added the license plates. I'd just put them in car specs. In general they are car related but may change sometime in future. If they change quite often I'd rather move them in a dedicated table too. MySQL Workbench Sheet Screenshot

I would actually save the price per renting day depending on the overall renting span to the db. That way, you could do something like

SELECT price FROM prices
WHERE car_id = 123 AND days = MAX(days);

That way you could multiply the "last" price with the actual amount of renting days for any rents above 30 days. But thats up to pricing definitions.