如何规范化我的MySQL数据库

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

我有PHP/MYSQL租车网站。在MYSQL表中存储

car license plates
汽车牌照
car specs (like AC, brand and such)
汽车规格(如AC、品牌等)
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
每天的价格(30列)，因为1天的价格是X欧/天，30天的价格是Y欧/天
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
每日保险(这是每辆车的保险，因为它取决于特定的汽车历史、年份、品牌、车型等)。因为每个月有30天，这里还有30列，因为1天的保险<> 28天的保险

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.

现在如果我把这些都放进去，就会有70列。有没有更聪明的方法来避免性能上的打击?我不控制价格，也没有每日价格或每日保险公式。

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?

一个想法是用汽车牌照作为一个指标，并把它吹到两张桌子上，一个有价格(35行)，一个有保险(35行)。任何其他的吗?

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

DB有1000辆左右的车。我每天在DB中得到大约10,000个查询

Kind thanks.

谢谢。

2 个解决方案

#1

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

下面是一个卑鄙的尝试。我将把价格和保险费用放在专用表中，每个表都有一个car_id和days字段。

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.

更新:添加了车牌。我只是把它们放在汽车规格里。一般来说，它们与汽车有关，但将来可能会改变。如果它们经常变化，我宁愿把它们放在专用的桌子上。

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

实际上，我可以根据db的总租用周期来节省每个租用日的价格。这样，你可以做一些类似的事情

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.

这样你就可以把“最后”的价格乘以30天以上的租金。但这取决于定价定义。

#2

To normalize a database (or more general to design a database) you have to, clearly, determine the entities that you have.

要规范化数据库(或设计数据库的更一般)，您必须明确地确定您拥有的实体。

From your description, you have four entities as follows:

从您的描述中，您有以下四个实体:

Car
车
Specification
规范
Price
价格
Insurance
保险

Every entity of the above should has a table to handle its properties (columns).

上面的每个实体都应该有一个表来处理它的属性(列)。

After, defining the entities, the real normalization is to define the relations between the entities, either through, keys properties (columns) or through new entity (table), for example, in Many to Many relations. Lets discus the relations:

定义实体之后，真正的规范化是通过键属性(列)或通过新实体(表)定义实体之间的关系，例如，在许多到许多关系中。让铁饼的关系:

Car: one car should has one specification, price and insurance. i.e the relation is one to one. So, cars table should has specification_id, price_id and insurance_id columns that relate it with the other three tables.

汽车:一辆车应该有一个规格，价格和保险。我。这个关系是一对一的。因此，cars表应该具有与其他三个表相关的speciation_id、price_id和insurance_id列。
On the other hand the other three tables (entities: specifications, price and insurance) may have many cars so its relation to car is one to many and it is covered by defining the foreign keys in the cars table (specification_id, price_id and insurance_id)

另一方面，其他三个表(实体:规格、价格和保险)可能有很多辆车，所以它与汽车的关系是一对一的，并且通过在cars表中定义外键(specific _id、price_id和insurance_id)来覆盖它。

How could it work?

它怎么工作的?

Before inserting new car, you have to complete the other entities. In other words, a list of all available, specifications, prices, insurances should be found in there respectable tables (entities) and if you have got a new car that has no any defined one of them, then you have to create new entity that covers its need before inserting or creating it. i.e inserting new specification and/or new price and/or new insurance that car should belong to.

在插入新汽车之前，您必须完成其他实体。换句话说,所有可用的列表,规格,价格,保险应该发现在受人尊敬的表(实体),如果你有一辆新车,没有任何定义的其中一个,然后你必须创建新的实体,其需要在插入或创建它。我。插入汽车应该属于的新规格和/或新价格和/或新保险。

Notice: this is not a law, you or other one may able to invent another entities relations. However, what I have regarded here is a general hint based on relational database design methodology

注意:这不是一条法律，你或其他任何人都可以发明另一个实体关系。然而，我在这里看到的是基于关系数据库设计方法的一个一般提示

#1