git.net

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

Re: data modeling appointment scheduling


Well, generally speaking I like to understand the problem before trying to fit a solution.  If you're looking to set up millions of appointments for a business, that might quality for some amount of partitioning / bucketing.  That said, you might be better off using time based buckets, say monthly or yearly, and as part of the process consider the worst case scenario for data size.  Is there a chance that in a given month there will be more than 50MB of data associated with a single account / entity? 

If you design the table using the startdatetime as the clustering key, you'll get your events back in the order they are scheduled, which has obvious advantages but does come at the cost of increased complexity when updating the start time.  The short answer is - you can't update it, you have to delete the record and re-insert it with the updated data (you can't update a clustering key).

Hope this helps.
Jon



On Sun, Nov 4, 2018 at 2:28 PM I PVP <ipvp@xxxxxxxxxxx> wrote:
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 ? 

thanks.

IPVP

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.

Thanks

IPVP


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


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