git.net

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: data modeling appointment scheduling


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.

Thanks

IPVP


--
Jon Haddad
http://www.rustyrazorblade.com
twitter: rustyrazorblade