用于租赁可用性的 Mysql 结构
我必须将 30,000 个地点的可用性日历导入 MySQL，并且我陷入了结构设计。我需要一些东西，让我可以轻松地进行子查询并加入给定日期的签入可用性。
实际上，每天都有几个选项
- 可以 checkIn 和 CheckOut
- 不可用
- CanCheckIn 只能
- CanCheckOut
- OnRequest
现在对于一个表来说，最优化的解决方案是什么？
PlaceId Day AvailabilityCode ???
那么我会有 366 * 30, 000 行？我担心这个。
有没有更好的办法呢？
我应该解析的 xml 数据如下所示
<?xml version="1.0" encoding="utf-8" ?>
<vacancies>
<vacancy>
<code>AT1010.200.1</code>
<startday>2010-07-01</startday>
<availability>YYYNNNQQ</availability>
<changeover>CCIIOOX</changeover>
<minstay>GGGGGGGG</minstay>
<flexbooking>YYYYY</flexbooking>
</vacancy>
</vacancies>
，其中 
重要的附加信息： 问题是可用日历以 XML 源的形式给出，我必须每 10-20 分钟导入它并重新填充我的数据库。
I have to import an availability calendar of 30,000 places into MySQL, and I am stuck on structure design. I need something which will allow me to easily subquery and join availability of checkIn for a given date.
Actually, each day has several options
- Can checkIn and CheckOut
- Not Available
- CanCheckIn only
- CanCheckOut
- OnRequest
now what would be a most optimal solution for a table?
PlaceId Day AvailabilityCode ???
Then I would have 366 * 30, 000 rows? I am afraid of that.
Is there any better way to do?
The xml data I should parse looks like this
<?xml version="1.0" encoding="utf-8" ?>
<vacancies>
<vacancy>
<code>AT1010.200.1</code>
<startday>2010-07-01</startday>
<availability>YYYNNNQQ</availability>
<changeover>CCIIOOX</changeover>
<minstay>GGGGGGGG</minstay>
<flexbooking>YYYYY</flexbooking>
</vacancy>
</vacancies>
Where
Crucial additional information: The problem is that the availability calendar is given as an XML feed, and I have to import it and repopulate my database each 10-20 minutes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我认为你的问题是 XML feed，而不是表结构。最简单的解决方案是要求 Feed 提供商仅提供增量而不是整个转储。但想必有充分的理由证明这是不可能的。
所以你必须这样做。您应该以某种方式存储 XML 提要，并将新文件与前一个文件进行比较。这将为您提供增量，然后您可以将其应用到数据库表中。您可以采取多种方法，您选择哪种方法在很大程度上取决于您的编程能力以及数据库产品的功能。
例如，MySQL 自 5.1 以来仅具有 XML 功能，并且它仍然是相当有限。因此，如果您想预处理 XML 文件，您可能必须在数据库之外进行。另一种方法是将最新文件加载到临时表中，并使用 SQL 查找并应用差异。
I think your problem is the XML feed, not the table structure. The easiest solution would be to ask the feed provider to deliver just a delta rather than a whole dump. But presumably there's a good reason why that is not possible.
So you will have to do it. You should store the XML feeds somehow, and compare the new file with the previous one. This will give you the delta, which you can then apply to your database table. There are several approaches you could take, and which you choose will largely depend on your programming prowess, and the capabilities of your database product.
For instance, MySQL has only had XML functionality since 5.1 and it is still pretty limited. So if you want to preprocess the XML file you will probably have to do it outside the database. An alternative approach would be to load the latest file into a staging table and use SQL to find and apply the differences.
您只需要在某些内容不可用时添加行。缺少日期和房间的行可以隐式解释为可用性
you only need to add rows when something is not available. A missing row for a date and room can be implicitly interpreted as availability
365 * 30000 是一个表中只有小字段（int id、日期或日期以及代码，也可能是 int 或可能是 char(1)）的表中超过 1000 万条记录。这在 MySQL 中是非常可行的，并且只有当您对此表进行多次读取和频繁更新时才会成为问题。如果只是偶尔更新的话，10、2000万条记录的表也没有太大问题。
但也许有更好的解决方案，尽管它可能更复杂。
在我看来，这听起来像是某种预订计划。如果是这样，每个地方可能都会有可以预订的季节。您可以为每个地点指定一个默认值，甚至可以为每个季节指定一个默认值。例如，3月至8月有一个位置可用，其他月份则不可用。然后，当夏季预订了某个位置但该位置不再可用时，您可以将该值放入您建议的表中。
这样，您就可以检查指定日期所请求地点是否存在记录。如果不存在，则检查“地点”表（30000 条记录）或“季节”表中的默认值，其中每个地点每个季节有一条记录（每个地点可能有 2 到 4 条记录）。这样你就可以减少很多记录的数量。
但请记住，如果您几乎每天都收到每个地点的预订，则此方法将不起作用。在这种情况下，您几乎不需要默认值，并且每日状态表中仍将有数百万条记录。正如我之前所说，这可能根本不是问题，但无论如何您应该考虑更复杂的解决方案是否确实可以帮助您减少数据。这取决于你的情况。
365 * 30000 is a little over 10 million records in a table with only small fields (int id, date or day, and a code, which is probably an int as well or maybe a char(1)). This is very doable in MySQL and will only become a problem if you got many reads and frequent updates to this table. If it is only updates now and then, it will not be much of a problem to have tables with 10 or 20 million records.
But maybe there's a better solution, although it may be more complex.
It sounds to me like some soort of booking programme. If so, each place will probably have seasons in which they can be booked. You can give each place a default value, or maybe even a default value per season. For instance, a place is available from march to august, and unavailable in the other months. Then, when a place is booked during the summer and it becomes unavailable, you can put that value in the table you suggested.
That way, you can check if a record exists for a given day for the requested place. If it does not exist, you check the default value in the 'places' table (30000 records), or the 'seasons' table where you got a record per season per place (maybe 2 to 4 records per place). That way you can cut the number of records down by a lot.
But remember this will not work if you got bookings for almost every day for each place. In that case, you will hardly ever need the defaults, and there will still be millions of records in the state-per-day table. Like I said before, this may not be a problem at all, but anyway you should consider whether the more complex solution will indeed help you decrease the data or not. It depends on you situation.