I am currently working on a web application that stores information of Cooks in the user table. We have a functionality to search the cooks from our web application. If a cook is not available on May 3, 2016, we want to show the Not-Bookable or Not-Available message for that cook if user performs the search for May 3, 2016. The solution we have come up to is to create a table named CooksAvailability with following fields
我目前正在开发一个web应用程序，该应用程序将厨师的信息存储在用户表中。我们有从web应用程序中搜索厨师的功能。如果在2016年5月3日没有厨师，如果用户在2016年5月3日进行搜索，我们想为厨师显示不可预订或不可预订的消息。我们得到的解决方案是创建一个名为CooksAvailability的表，其中包含以下字段
ID, //Primary key, auto increment
IDCook, //foreign key to user's table
Date, //date he is available on
AvailableForBreakFast, //bool field
AvailableForLunch, //bool field
AvailableForDinner, //book field
BreakFastCookingPrice, //decimal nullable
LunchCookingPrice, //decimal nullable
DinnerCookingPrice //decimal nullable
With this schema, we are able to tell if the user is available for a specific date or not. But the problem with this approach is that it requires a lot of db space i.e if a cook is available for 280 days/year, there has to be 280 rows to reflect just one cook's availability.
有了这个模式，我们就可以知道用户在特定的日期是否可用。但是这种方法的问题是它需要大量的db空间i。如果一个厨师的时间是280天/年，那么就必须有280行来反映一个厨师的可用性。
This is too much space given the fact that we may have potentially thousands of cooks registered with our application. As you can see the CookingPrice fields for breakfast, lunch and dinner. it means a cook can charge different cooking rates for cooking on different dates and times.
考虑到我们可能在应用程序中注册了数千名厨师，这就占用了太多空间。你可以看到早餐、午餐和晚餐的菜价。这意味着厨师可以根据不同的日期和时间对不同的烹饪收费。
Currently, we are looking for a smart solution that fulfils our requirements and consumes less space than our solution does.
目前，我们正在寻找一种智能解决方案，它能够满足我们的需求，并且比我们的解决方案占用更少的空间。
3 个解决方案
#1
2
You are storing a record for each day and the main mistake, which led you to this redundant design was that you did not separate the concepts enough.
您正在存储每天的记录，主要的错误是，您没有足够地分离概念，这导致了这个冗余设计。
I do not know whether a cook has an expected rate for a given meal, that is, a price one can assume in general if one has no additional information. If that is the case, then you can store these default prices in the table where you store the cooks.
我不知道一个厨师对一顿饭是否有一个预期的价格，也就是说，如果没有额外的信息，一个人可以假定这个价格。如果是这样，那么您可以将这些默认价格存储在存储厨师的表中。
Let's store the availability and the specific prices in different tables. If the availability does not have to store the prices, then you can store availability intervals. In the other table, where you store the prices, you need to store only the prices which deviate from the expected price. So, you will have defined availability intervals in a table, specific prices when the price differs from the expected one in the oter and default meal price values in the cook table, so, if there is no special price, the default price will be used.
让我们将可用性和特定价格存储在不同的表中。如果可用性不需要存储价格，那么您可以存储可用性间隔。在另一个表中，存储价格时，只需要存储偏离预期价格的价格。因此，您将在一个表中定义可用性间隔，当价格与oter中的预期值不同，以及在cook表中默认的餐饮价格值时，价格会有所不同，因此，如果没有特别的价格，将使用默认价格。
#2
2
To answer your question I should know more about the structure of the information. For example if most cooks are available in a certain period, it could be helpful to organize your availability table with
为了回答你的问题，我应该多了解一些信息的结构。例如，如果大多数厨师在某段时间内都可以使用，那么组织您的可用性表可能会很有帮助
avail_from_date - avail_to_date, instead of a row for each day.
avail_from_date - avail_to_date，而不是每天的一行。
this would reduce the amount of rows.
这将减少行数。
The different prices for breakfast, lunch and dinner could be stored better in the cooks table, if the prices are not different each day. Same is for the a availability for breakfast, lunch and dinner if this is not different each day.
早餐、午餐和晚餐的不同价格，如果每天的价格没有不同，可以更好地储存在厨师的餐桌上。如果每天都是一样的话，那么早餐、午餐和晚餐的供应也是一样的。
But if your information structure makes it necessary to keep a record for every cook every day this would be 365 * 280 = 102,200 records for a year, this is not very much for a sql db in my eyes. If you put the indexes at the right place this will have a good performance.
但是，如果你的信息结构使你有必要记录每一天每一个厨师的记录，这将是365 * 280 = 102,200记录一年，这对于我的眼睛来说不是很大。如果把索引放在正确的位置，这将具有良好的性能。
#3
0
There are a few questions that would help with the overall answer.
有几个问题可以帮助你得到整体的答案。
- How often does availability change?
- 可用性多久改变一次?
- How often does price change?
- 价格多久变化一次?
- Are there general patterns, e.g. cook X is available for breakfast and lunch, Monday - Wednesday each week?
- 有一般的模式吗?例如，每周周一到周三的早餐和午餐都有。
- Is there a normal availability / price over a certain period of time, but with short-term overrides / differences?
- 在一段时间内是否有一个正常的可用性/价格，但是有短期的覆盖/差异?
If availability and price change at different speeds, I would suggest you model them separately. That way you only need to show what has changed, rather than duplicating data that is constant.
如果可用性和价格以不同的速度变化，我建议您分别建模。这样，您只需要显示已更改的内容，而不需要复制常量数据。
Beyond that, there's a space / complexity trade-off to make.
除此之外，还需要进行空间/复杂性的权衡。
At one extreme, you could have a hierarchy of configurations that override each other. So, for cook X there's set A that says they can do breakfast Monday - Wednesday between dates 1 and 2. Then also for cook X there's set B that says they can do lunch on Thursday between dates 3 and 4. Assuming that dates go 1 -> 3 -> 4 -> 2, you can define whether set B overrides set A or adds to it. This is the most concise, but has quite a lot of business logic to work through to interpret it.
在一个极端，您可以拥有一个相互覆盖的配置层次结构。所以，对于厨师X，有一个固定的A说他们可以在周一到周三的第一天到第二天做早餐。另外，对于cook X，有一个B，说他们可以在周四的日期3到4之间吃午饭。假设日期为1 -> 3 -> 4 -> 2，您可以定义set B是否覆盖set A或添加它。这是最简洁的，但是有很多业务逻辑需要解释。
At the other extreme, you just say for cook X between date 1 and 2 this thing is true (an availability for a service, a price). You find all things that are true for a given date, possibly bringing in several separate records e.g. a lunch availability for Monday, a lunch price for Monday etc.
在另一个极端，您只需对cook X说在日期1到日期2之间这是正确的(一个服务的可用性，一个价格)。你会发现约会中所有的事情都是真实的，可能会带来不同的记录，比如周一的午餐时间，周一的午餐价格等等。
#1
2
You are storing a record for each day and the main mistake, which led you to this redundant design was that you did not separate the concepts enough.
您正在存储每天的记录，主要的错误是，您没有足够地分离概念，这导致了这个冗余设计。
I do not know whether a cook has an expected rate for a given meal, that is, a price one can assume in general if one has no additional information. If that is the case, then you can store these default prices in the table where you store the cooks.
我不知道一个厨师对一顿饭是否有一个预期的价格，也就是说，如果没有额外的信息，一个人可以假定这个价格。如果是这样，那么您可以将这些默认价格存储在存储厨师的表中。
Let's store the availability and the specific prices in different tables. If the availability does not have to store the prices, then you can store availability intervals. In the other table, where you store the prices, you need to store only the prices which deviate from the expected price. So, you will have defined availability intervals in a table, specific prices when the price differs from the expected one in the oter and default meal price values in the cook table, so, if there is no special price, the default price will be used.
让我们将可用性和特定价格存储在不同的表中。如果可用性不需要存储价格，那么您可以存储可用性间隔。在另一个表中，存储价格时，只需要存储偏离预期价格的价格。因此，您将在一个表中定义可用性间隔，当价格与oter中的预期值不同，以及在cook表中默认的餐饮价格值时，价格会有所不同，因此，如果没有特别的价格，将使用默认价格。
#2
2
To answer your question I should know more about the structure of the information. For example if most cooks are available in a certain period, it could be helpful to organize your availability table with
为了回答你的问题，我应该多了解一些信息的结构。例如，如果大多数厨师在某段时间内都可以使用，那么组织您的可用性表可能会很有帮助
avail_from_date - avail_to_date, instead of a row for each day.
avail_from_date - avail_to_date，而不是每天的一行。
this would reduce the amount of rows.
这将减少行数。
The different prices for breakfast, lunch and dinner could be stored better in the cooks table, if the prices are not different each day. Same is for the a availability for breakfast, lunch and dinner if this is not different each day.
早餐、午餐和晚餐的不同价格，如果每天的价格没有不同，可以更好地储存在厨师的餐桌上。如果每天都是一样的话，那么早餐、午餐和晚餐的供应也是一样的。
But if your information structure makes it necessary to keep a record for every cook every day this would be 365 * 280 = 102,200 records for a year, this is not very much for a sql db in my eyes. If you put the indexes at the right place this will have a good performance.
但是，如果你的信息结构使你有必要记录每一天每一个厨师的记录，这将是365 * 280 = 102,200记录一年，这对于我的眼睛来说不是很大。如果把索引放在正确的位置，这将具有良好的性能。
#3
0
There are a few questions that would help with the overall answer.
有几个问题可以帮助你得到整体的答案。
- How often does availability change?
- 可用性多久改变一次?
- How often does price change?
- 价格多久变化一次?
- Are there general patterns, e.g. cook X is available for breakfast and lunch, Monday - Wednesday each week?
- 有一般的模式吗?例如，每周周一到周三的早餐和午餐都有。
- Is there a normal availability / price over a certain period of time, but with short-term overrides / differences?
- 在一段时间内是否有一个正常的可用性/价格，但是有短期的覆盖/差异?
If availability and price change at different speeds, I would suggest you model them separately. That way you only need to show what has changed, rather than duplicating data that is constant.
如果可用性和价格以不同的速度变化，我建议您分别建模。这样，您只需要显示已更改的内容，而不需要复制常量数据。
Beyond that, there's a space / complexity trade-off to make.
除此之外，还需要进行空间/复杂性的权衡。
At one extreme, you could have a hierarchy of configurations that override each other. So, for cook X there's set A that says they can do breakfast Monday - Wednesday between dates 1 and 2. Then also for cook X there's set B that says they can do lunch on Thursday between dates 3 and 4. Assuming that dates go 1 -> 3 -> 4 -> 2, you can define whether set B overrides set A or adds to it. This is the most concise, but has quite a lot of business logic to work through to interpret it.
在一个极端，您可以拥有一个相互覆盖的配置层次结构。所以，对于厨师X，有一个固定的A说他们可以在周一到周三的第一天到第二天做早餐。另外，对于cook X，有一个B，说他们可以在周四的日期3到4之间吃午饭。假设日期为1 -> 3 -> 4 -> 2，您可以定义set B是否覆盖set A或添加它。这是最简洁的，但是有很多业务逻辑需要解释。
At the other extreme, you just say for cook X between date 1 and 2 this thing is true (an availability for a service, a price). You find all things that are true for a given date, possibly bringing in several separate records e.g. a lunch availability for Monday, a lunch price for Monday etc.
在另一个极端，您只需对cook X说在日期1到日期2之间这是正确的(一个服务的可用性，一个价格)。你会发现约会中所有的事情都是真实的，可能会带来不同的记录，比如周一的午餐时间，周一的午餐价格等等。