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.
我需要在Java中建立一个程序，使用MySQL存储医生的预约。我有一个粗略的想法，我需要一张桌子来记录医生的id、病人的id和预约日期和时间。我是否遗漏了其他属性?我假设在我的Java实现中需要一个compareTo方法来检查可用的时间。我希望我已经说清楚了我想要完成的事情。目前，我有一个医生表和一个病人表，都有他们自己的id作为他们的主键。我使用Hibernate在MySQL中映射面向对象的设计，如果这很重要的话。
1 个解决方案
#1
2
Date-Time Is One Value
Date-time values are almost always tracked in software as single values. Technically they are represented internally as a count of seconds/milliseconds/microseconds/nanoseconds since an epoch.
在软件中，日期时间值几乎总是以单一值的形式被跟踪。从技术上讲，它们在内部被表示为秒数/毫秒/微秒/毫微秒。
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.
而且，你几乎肯定应该考虑时区。天真的程序员通常认为他们可以忽略时区，但这几乎肯定会导致后来的痛苦。
Understand Your Database’s Handling of Date-Time
Different databases handle date-time differently. Absolutely crucial that you read the docs, play around, experiment, and learn exactly how your database works.
不同的数据库以不同的方式处理日期时间。绝对重要的是，您可以阅读文档，进行操作，进行实验，并准确地了解您的数据库是如何工作的。
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.
Postgres对日期时间的处理非常出色和合理。即使您使用了另一个数据库，也要查阅优秀的Postgres文档，以了解关于日期时间数据类型和日期时间函数(命令)，了解各种问题，以及SQL标准对您的数据库特有的定义。
Store Globally, Present Locally
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".
日期时间是一个非常棘手和复杂的问题。解决这个问题的一个关键是在UTC工作。将您的日期时间值存储在数据库中(或在序列化文件中，或XML/JSON通信中)。把你的大部分业务逻辑写在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.
当您呈现给用户时，可以使用ISO 8601格式或本地化到自己的时区(或者他们期望的时区)。这遵循了国际化/本地化的基本思想。对于文本值，在代码中使用某些关键字符串。在用户界面上显示的时候，您将这些内部字符串映射到用户界面的本地化(翻译)文本值。一些与日期时间: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.
需要注意的是:为了历史，您可能还需要存储本地日期时间。由于政客和官僚，时区规则经常变化无常。您的软件的时区数据库可能过时了。因此，您可能想要存储您或用户认为的特定日期时间。但不要依赖它;确定并存储UTC值。
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.
小贴士:学会在24小时内思考和阅读。作为程序员/调试器/sysadmin，您的生活将变得更加容易和容易出错。
Joda-Time or java.time
The java.util.Date and .Calendar classes bundled with Java are notoriously troublesome. Avoid them.
java.util。与Java绑定的日期和. calendar类是出了名的麻烦。避免它们。
Instead use either Joda-Time or the new java.time package built into Java 8 (inspired by Joda-Time, defined by JSR 310).
可以使用Joda-Time或新的java。Java 8内置的时间包(由JSR 310定义的Joda-Time设计)。
Both libraries use ISO 8601 formats as their defaults, for both parsing and generating strings.
这两个库都使用ISO 8601格式作为它们的默认值，用于解析和生成字符串。
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.
ISO 8601是一个合理的标准，它定义了如何在特定的和非模糊的文本格式中显示日期时间值、时区和偏移量、持续时间和周期。好好研究一下*。
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.
特别要注意的是标准调用的时间。在此格式中定义了一个时间跨度:PnYnMnDTnHnMnS，其中P为“Period”，T将日期部分与时间部分分开，其他可选部分为数字+字母。半个小时的预约将是PT30M。这对您来说可能很方便，比如下面我的ERD中所看到的“period_”字段。在Joda-Time中，Period类通过跟踪它的月、日、小时等来表示一段时间，并且知道如何以这种格式解析和生成字符串。
Half-Open
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.
您可以选择以两种方式存储约会。一种方式是开始日期时间&持续时间(90分钟，20分钟，等等)。另一种方法是记录开始和停止日期。在这种情况下，通常最好的方法叫做“半开”。这意味着开始是包容的，而结尾是排外的。
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.
例如，一个小时的约会时间是11:00到12:00，意思是“从上午11点开始，然后跑到，但不包括下一个小时(中午)的第一个时刻”。下一个约会将从12:00到13:00。
Search * for "Half-open" to find more discussion and examples and diagrams.
搜索*为“半开”寻找更多的讨论，例子和图表。
Many-To-Many
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.
您需要知道如何在多对多关系中执行连接。
Direct SQL
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.
如果您是新编程或新到关系数据库，我建议避免Hibernate。你真的应该了解正在发生的事情。Hibernate有一些适当的用途。但是如果你认为Hibernate会神奇地使数据库问题消失，你会失望的。
Attributes
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.
预约调度是一个非常困难的业务问题，需要编写软件。例如，您是否只是简单地记录预约?或者，您是否通过创建预定义的时间段来跟踪医生的可用性，如果是这样，您如何处理对每个医生日历的异常和更改?您需要编写非常具体的需求和用例。用户的期望很容易超出您的预期要求。
Here's a simplistic view.
这是一个简单的视图。
#1
2
Date-Time Is One Value
Date-time values are almost always tracked in software as single values. Technically they are represented internally as a count of seconds/milliseconds/microseconds/nanoseconds since an epoch.
在软件中，日期时间值几乎总是以单一值的形式被跟踪。从技术上讲，它们在内部被表示为秒数/毫秒/微秒/毫微秒。
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.
而且，你几乎肯定应该考虑时区。天真的程序员通常认为他们可以忽略时区，但这几乎肯定会导致后来的痛苦。
Understand Your Database’s Handling of Date-Time
Different databases handle date-time differently. Absolutely crucial that you read the docs, play around, experiment, and learn exactly how your database works.
不同的数据库以不同的方式处理日期时间。绝对重要的是，您可以阅读文档，进行操作，进行实验，并准确地了解您的数据库是如何工作的。
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.
Postgres对日期时间的处理非常出色和合理。即使您使用了另一个数据库，也要查阅优秀的Postgres文档，以了解关于日期时间数据类型和日期时间函数(命令)，了解各种问题，以及SQL标准对您的数据库特有的定义。
Store Globally, Present Locally
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".
日期时间是一个非常棘手和复杂的问题。解决这个问题的一个关键是在UTC工作。将您的日期时间值存储在数据库中(或在序列化文件中，或XML/JSON通信中)。把你的大部分业务逻辑写在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.
当您呈现给用户时，可以使用ISO 8601格式或本地化到自己的时区(或者他们期望的时区)。这遵循了国际化/本地化的基本思想。对于文本值，在代码中使用某些关键字符串。在用户界面上显示的时候，您将这些内部字符串映射到用户界面的本地化(翻译)文本值。一些与日期时间: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.
需要注意的是:为了历史，您可能还需要存储本地日期时间。由于政客和官僚，时区规则经常变化无常。您的软件的时区数据库可能过时了。因此，您可能想要存储您或用户认为的特定日期时间。但不要依赖它;确定并存储UTC值。
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.
小贴士:学会在24小时内思考和阅读。作为程序员/调试器/sysadmin，您的生活将变得更加容易和容易出错。
Joda-Time or java.time
The java.util.Date and .Calendar classes bundled with Java are notoriously troublesome. Avoid them.
java.util。与Java绑定的日期和. calendar类是出了名的麻烦。避免它们。
Instead use either Joda-Time or the new java.time package built into Java 8 (inspired by Joda-Time, defined by JSR 310).
可以使用Joda-Time或新的java。Java 8内置的时间包(由JSR 310定义的Joda-Time设计)。
Both libraries use ISO 8601 formats as their defaults, for both parsing and generating strings.
这两个库都使用ISO 8601格式作为它们的默认值，用于解析和生成字符串。
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.
ISO 8601是一个合理的标准，它定义了如何在特定的和非模糊的文本格式中显示日期时间值、时区和偏移量、持续时间和周期。好好研究一下*。
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.
特别要注意的是标准调用的时间。在此格式中定义了一个时间跨度:PnYnMnDTnHnMnS，其中P为“Period”，T将日期部分与时间部分分开，其他可选部分为数字+字母。半个小时的预约将是PT30M。这对您来说可能很方便，比如下面我的ERD中所看到的“period_”字段。在Joda-Time中，Period类通过跟踪它的月、日、小时等来表示一段时间，并且知道如何以这种格式解析和生成字符串。
Half-Open
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.
您可以选择以两种方式存储约会。一种方式是开始日期时间&持续时间(90分钟，20分钟，等等)。另一种方法是记录开始和停止日期。在这种情况下，通常最好的方法叫做“半开”。这意味着开始是包容的，而结尾是排外的。
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.
例如，一个小时的约会时间是11:00到12:00，意思是“从上午11点开始，然后跑到，但不包括下一个小时(中午)的第一个时刻”。下一个约会将从12:00到13:00。
Search * for "Half-open" to find more discussion and examples and diagrams.
搜索*为“半开”寻找更多的讨论，例子和图表。
Many-To-Many
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.
您需要知道如何在多对多关系中执行连接。
Direct SQL
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.
如果您是新编程或新到关系数据库，我建议避免Hibernate。你真的应该了解正在发生的事情。Hibernate有一些适当的用途。但是如果你认为Hibernate会神奇地使数据库问题消失，你会失望的。
Attributes
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.
预约调度是一个非常困难的业务问题，需要编写软件。例如，您是否只是简单地记录预约?或者，您是否通过创建预定义的时间段来跟踪医生的可用性，如果是这样，您如何处理对每个医生日历的异常和更改?您需要编写非常具体的需求和用例。用户的期望很容易超出您的预期要求。
Here's a simplistic view.
这是一个简单的视图。