For people(invitee), you are correct. They will not have millions of appointments. But, the organizer is a business.. a chain of businesses (Franchisor and Franchisees) that together across the country have dozens of thousands of appointments per day.
Do you suggest removing the bucket , making the startdatetime clustering key and quering against the startdatetime with > and <?
Wouldn't still have the issue to be able to update startdatetime when an appointment gets rescheduled ?
On November 4, 2018 at 7:25:05 PM, Jonathan Haddad (jon@xxxxxxxxxxxxx) wrote:Maybe I’m missing something, but it seems to me that the bucket might be a little overkill for a scheduling system. Do you expect people to have millions of appointments?--
On Sun, Nov 4, 2018 at 12:46 PM I PVP <ipvp@xxxxxxxxxxx> wrote:
Could you please provide advice on the modeling approach for the following appointment scheduling scenario?
I am struggling to model in an way that allows to satisfy the requirement to be able to update an appointment, specially to be able to change the start datetime and consequently the bucket.
Queries/requirements:1)The ability to select all appointments by invitee and by date range on the start date
2)The ability to select all appointments by organizer and by date range on the start date
3)The ability to update(date, location, status) of an specific appointment.
4)The ability to delete an specific appointment
Note: The bucket column is intended to allow date querying and to help spread data evenly around the cluster. The bucket value is composed by year+month+day sample bucket value: 20181104 )
CREATE TABLE appointment_by_invitee(objectid timeuuid,organizerid timeuuid,inviteeid timeuuid,bucket bigint,status text,location text,startdatetime timestamp,enddatetime timestamp,PRIMARY KEY ((inviteeid, bucket), objectid));
CREATE TABLE appointment_by_organizer(objectid timeuuid,organizerid timeuuid,inviteeid timeuuid,bucket bigint,status text,location text,startdatetime timestamp,enddatetime timestamp,PRIMARY KEY ((organizerid, bucket), objectid));
Any help will be appreciated.