java doctor appointment reservation database (mysql) program..having trouble designing the appointments schema -

i need build program in java uses mysql store doctors' appointments. have rough idea need table keep track of doctor's id, patient's id, , appointment date , time in same table. missing other attributes? assume need compareto method in java implementation check available times. hope have made clear want accomplish. currently, have doctor table , patient table, both having own id primary key. using hibernate map object oriented design relations in mysql if matters.

date-time 1 value

date-time values tracked in software single values. technically represented internally count of seconds/milliseconds/microseconds/nanoseconds since epoch.

you may want present date , time separately in user interface, not internally.

also, should thinking time zones. naïve programmers think can ignore time zones, cause anguish later.

understand database’s handling of date-time

different databases handle date-time differently. absolutely crucial read docs, play around, experiment, , learn how database works.

postgres has excellent , sensible handling of date-time. if use database, consult excellent postgres documentation on date-time data types , date-time functions (commands) learn various issues , defined sql standard versus peculiar database.

store globally, present locally

date-time surprisingly slippery , complicated problem. 1 key keeping hold on problem working in utc. store date-time values in database (or in serialized files, or xml/json communications) in utc. write of business logic in utc, except local time zone matters such defining "the beginning of new day".

when present user, either use iso 8601 format or localize own time zone (or time zone expect). follows basic idea of internationalization/localization. text values, use key strings in code. upon presentation in user interface, map internal strings localized (translated) text values user interface. date-time: utc internally, local time zone in user interface.

one caveat: might want also store local date-time sake of history. time zone rules change , capriciously because of politicians , bureaucrats. software's time zone database may out of date. may want store or user believed date-time then. don't rely on it; determine , store utc value.

tip: learn think , read in 24-hour time. life programmer/debugger/sysadmin become easier , less error-prone.

joda-time or java.time

the java.util.date , .calendar classes bundled java notoriously troublesome. avoid them.

instead use either joda-time or new java.time package built java 8 (inspired joda-time, defined jsr 310).

both libraries use iso 8601 formats defaults, both parsing , generating strings.

iso 8601

iso 8601 sensible standard defining how present date-time values, time zones , offsets, durations, , periods in specific , non-ambiguous textual formats. study well-written wikipedia page.

note in particular standard calls durations. span of time defined in format: pnynmndtnhnmns p means "period", t separates date portion time portion, , other optional parts digits + letter. half-hour appointment pt30m. may handy you, such "period_" field seen in erd below. in joda-time, period class represents span of time tracking months, days, hours, etc., , knows how both parse , generate strings in format.

half-open

you can choose store appointments in either of 2 ways. 1 way start date-time & duration (90 minutes, 20 minutes, etc.). way record both start , stop date-time. in case, usual , best approach called "half-open". means beginning inclusive while ending exclusive.

for example, one-hour appointment on hour run 11:00 12:00, meaning "starting @ 11 , running to, not including, first moment of next hour (noon)". next appointment run 12:00 13:00.

search stackoverflow "half-open" find more discussion , examples , diagrams.

many-to-many

the relationship between patient , doctor call many-to-many. doctor sees many patients, , patient may see more 1 of doctors. sure know many-to-many tables in relational database design. solution add third table, called "bridge" table serves child table both of other parent tables. in case, appointment table bridge table.

you need know how perform joins across many-to-many relationship.

direct sql

if new programming or new relational database, suggest avoiding hibernate. should grasp of going on. hibernate has appropriate uses. if think hibernate going magically make database issues disappear, you’ll disappointed.

attributes

attributes you. depend on business (or homework?) problem trying solve. have basics right.

appointment-scheduling difficult business problem write software. example, recording appointments being made? or tracking availability of doctors, creating predefined time slots, , if how handle exceptions , changes each doctor’s calendar? need write specific requirements , use-cases. easy users’ expectations exceed supposed requirements.

here's simplistic view.