java医生预约预约数据库(mysql)程序..设计预约模式时遇到问题 [英] java doctor appointment reservation database (mysql) program..having trouble designing the appointments schema

问题描述

我需要用 Java 构建一个程序，该程序使用 MySQL 来存储医生的预约.我有一个粗略的想法，我需要一个表来跟踪同一张表中的医生 ID、患者 ID 以及约会日期和时间.我是否缺少任何其他属性?我假设我的 Java 实现中需要一个 compareTo 方法来检查可用时间.我希望我已经明确了我想要完成的任务.目前，我有一个 Doctor 表和一个 Patient 表，它们都有自己的 id 作为主键.如果重要的话，我正在使用 Hibernate 将面向对象的设计与我在 MySQL 中的关系进行映射.

I need to build a program in Java that uses MySQL to store the doctors' appointments. I have a rough idea that I need a table to keep track of the Doctor's id, Patient's id, and the appointment date and time in the same table. Am I missing any other attributes? I assume I would need a compareTo method in my Java implementation to check for the available times. I hope I have made it clear to what I want to accomplish. Currently, I have a Doctor table and a Patient table, both having their own id as their primary key. I am using Hibernate to map the object oriented design with my relations in MySQL if that matters.

推荐答案

Date-Time Is One Value

日期时间值几乎总是在软件中作为单个值进行跟踪.从技术上讲，它们在内部表示为自 纪元以来的秒数/毫秒/微秒/纳秒数.

您可能希望在用户界面中单独显示日期和时间，但不要在内部显示.

You may want to present a date and a time separately in the user interface, but not internally.

此外，您几乎肯定应该考虑时区.天真的程序员通常认为他们可以忽略时区，但这几乎肯定会在以后引起痛苦.

Also, you almost certainly should be thinking about time zones. Naïve programmers often think they can ignore time zones, but that is almost certain to cause anguish later.

不同的数据库以不同的方式处理日期时间.阅读文档、试用、实验并准确了解数据库的工作原理至关重要.

Different databases handle date-time differently. Absolutely crucial that you read the docs, play around, experiment, and learn exactly how your database works.

Postgres 对日期时间有出色而明智的处理.即使您使用其他数据库，请参阅关于 date-time 的优秀 Postgres 文档数据类型和日期时间函数(命令)以了解各种问题以及 SQL 标准定义的内容与数据库特有的内容.

Postgres has excellent and sensible handling of date-time. Even if you use another database, consult the excellent Postgres documentation on date-time data types and date-time functions (commands) to learn about the various issues and about what is defined by the SQL standard versus peculiar to your database.

日期-时间是一个令人惊讶的棘手和复杂的问题.解决问题的一个关键是在 UTC 下工作.以 UTC 格式将日期时间值存储在数据库中(或序列化文件或 XML/JSON 通信中).使用 UTC 编写大部分业务逻辑，除非本地时区很重要，例如定义新一天的开始".

Date-Time is a surprisingly slippery and complicated problem. One key to keeping a hold on the problem is working in UTC. Store your date-time values in the database (or in serialized files, or XML/JSON communications) in UTC. Write most of your business logic in UTC, except where local time zone matters such as defining "the beginning of a new day".

当您向用户展示时，要么使用 ISO 8601 格式，要么本地化为他们自己的时区(或他们期望的时区).这遵循国际化/本地化的基本思想.对于文本值，您可以在代码中使用某些键字符串.在用户界面中呈现时，您将这些内部字符串映射到用户界面的本地化(翻译)文本值.一些带有日期时间:内部UTC，用户界面中的本地时区.

When you present to the user, either use ISO 8601 format or localize to their own time zone (or the time zone they expect). This follows the basic idea of internationalization/localization. For text values, you use certain key strings in your code. Upon presentation in the user interface, you map those internal strings to localized (translated) text values for user interface. Some with date-time: UTC internally, local time zone in user interface.

一个警告:为了历史起见，您可能希望还存储本地日期时间.由于政客和官僚，时区规则经常变化无常.您的软件的时区数据库可能已过时.因此，您可能希望存储您或用户认为是某个日期时间然后的内容.但不要依赖它；确定并存储 UTC 值.

One caveat: You might want to also store a local date-time for the sake of history. Time zone rules change frequently and capriciously because of politicians and bureaucrats. Your software's time zone database may be out of date. So you may want to store what you or the user believed to be a certain date-time then. But don't rely on it; determine and store the UTC value.

提示:学会在 24 小时内思考和阅读.您作为程序员/调试员/系统管理员的生活将变得更加轻松且不易出错.

Tip: Learn to think and read in 24-hour time. Your life as a programmer/debugger/sysadmin will become some much easier and less error-prone.

Java 捆绑的 java.util.Date 和 .Calendar 类是出了名的麻烦.避开它们.

The java.util.Date and .Calendar classes bundled with Java are notoriously troublesome. Avoid them.

改为使用 Joda-Time 或新的 java.time 包内置于 Java 8(受 Joda-Time 启发，由 JSR 310 定义).

Instead use either Joda-Time or the new java.time package built into Java 8 (inspired by Joda-Time, defined by JSR 310).

两个库都使用 ISO 8601 格式作为它们的默认格式，用于解析和生成字符串.

Both libraries use ISO 8601 formats as their defaults, for both parsing and generating strings.

ISO 8601 是定义如何呈现日期时间值、时区的合理标准以及特定和非歧义文本格式的偏移量、持续时间和句点.研究写得很好的维基百科页面.

ISO 8601 is a sensible standard defining how to present date-time values, time zones and offsets, durations, and periods in specific and non-ambiguous textual formats. Study that well-written Wikipedia page.

请特别注意标准所称的持续时间.时间跨度以这种格式定义: PnYnMnDTnHnMnS 其中 P 表示Period"，T 将日期部分与时间部分分开，其他可选部分为数字+字母.半小时的预约将是 PT30M.这对您来说可能很方便，例如在我的 中看到的period_"字段ERD 下面.在 Joda-Time 中，Period 类通过跟踪其月、日、小时等来表示时间跨度，并且知道如何解析和生成这种格式的字符串.

Note in particular what the standard calls Durations. A span of time is defined in this format: PnYnMnDTnHnMnS where P means "Period", the T separates the date portion from the time portion, and the other optional parts are digits + letter. A half-hour appointment would be PT30M. This may be handy for you, such as for the "period_" field seen in my ERD below. In Joda-Time, the Period class represents a span of time by tracking its months, days, hours, etc., and knows how to both parse and generate strings in this format.

您可以选择以两种方式之一存储约会.一种方式是开始日期时间&持续时间(90 分钟、20 分钟等).另一种方法是记录开始和停止日期时间.在这种情况下，通常且通常最好的方法称为半开".这意味着开头是包容性，而结尾是排他性.

You can choose to store appointments in either of two ways. One way is a start date-time & a duration (90 minutes, 20 minutes, etc.). Another way is to record both a start and stop date-time. In this case, the usual and generally best approach is called "Half-Open". This means the beginning is inclusive while the ending is exclusive.

例如，整点一小时的约会将在 11:00 到 12:00 之间运行，这意味着从上午 11 点开始一直运行到但不包括下一小时的第一时刻(中午)".下次预约时间为 12:00 至 13:00.

For example, a one-hour appointment on the hour would run from 11:00 to 12:00, meaning "starting at 11 AM and running up to, but not including, the first moment of the next hour (noon)". The next appointment will run from 12:00 to 13:00.

在 StackOverflow 上搜索半开放"以找到更多讨论、示例和图表.

Search StackOverflow for "Half-open" to find more discussion and examples and diagrams.

患者和医生之间的关系就是我们所说的多对多.一个医生看很多病人，一个病人可能会看多个医生.确保您了解关系数据库设计中的多对多表.解决方案始终是添加第三个表，有时称为桥"表，用作其他两个父表的子表.在您的情况下，Appointment 表是桥接表.

The relationship between Patient and Doctor is what we call Many-To-Many. A doctor sees many patients, and a patient may see more than one of the doctors. Be sure you know about Many-To-Many tables in relational database design. The solution is always to add a third table, sometimes called a "bridge" table that serves as a child table to both of the other parent tables. In your case, the Appointment table is the bridge table.

您需要知道如何在多对多关系中执行连接.

You will need to know how to perform joins across a many-to-many relationship.

如果您是编程新手或关系数据库新手，我建议您避免使用 Hibernate.你真的应该了解正在发生的事情.Hibernate 有一些适当的用途.但是如果你认为 Hibernate 会神奇地让数据库问题消失，你会失望的.

If you are new to programming or new to relational database, I suggest avoiding Hibernate. You really should get a grasp of what is going on. Hibernate has some appropriate uses. But if you think Hibernate is going to magically make database issues disappear, you’ll be disappointed.

属性由您决定.它们取决于您要解决的业务(或家庭作业?)问题.你有基本的权利.

Attributes are up to you. They depend on the business (or homework?) problem you are trying to solve. You have the basics right.

约会安排是编写软件的一个非常困难的业务问题.例如，您是否只是在记录约会?或者您是否通过创建预定义的时间段来跟踪医生的可用性，如果是这样，您如何处理每个医生日历的异常和更改?您需要编写非常具体的要求和用例.用户的期望很容易超出您的假设要求.

Appointment-scheduling is a very difficult business problem for which to write software. For example, are you simply recording appointments being made? Or are you tracking the availability of the doctors, by creating predefined time slots, and if so how do you handle exceptions and changes to each doctor’s calendar? You need to write very specific requirements and use-cases. Very easy for users’ expectations to exceed your supposed requirements.

这是一个简单的视图.

这篇关于java医生预约预约数据库(mysql)程序..设计预约模式时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！