9 ms·
Database Design for Google Calendar: A Tutorial
- markus_zhang 2y agoInteresting. I wonder what does a NoSQL one look like?
- rcaught 2y agoIt looks like one you shouldn't implement.
- markus_zhang 2y agoMay I ask why? Actually I never used a NoSQL one so curious.
- irisgrunn 2y agoBecause events are related to users and they both are related to timezones and events can be related to each other. MongoDB is really good for storing big blobs of data you want to retrieve quickly, with some basic search and index, but it's awful at relations between data.
- markus_zhang 2y agoAh I see what you mean. That makes sense!
- Lutger 2y agoThe for most people somewhat counter-intuitive answer is that NoSql is very rigid. It is counter intuitive, because having no required schema up front appears to be more flexible, not less. However, having your database not handle schema means your application must do it, there is no way around it. If you ask for an DayEvent and you get back something totally different, what do you do? The rigidness in most NoSql (assuming some form of document store like MongoDB) comes from its inability to combine data in new ways in a performant manner (joins). This is what SQL excels at. That implies you need to design your data in exactly the way it is going to be consumed, because you can't easily recombine the pieces in different ways as you iterate your application. Generally you must know your data access patterns in advance to create a well behaved NoSql database. Changes are hard. This is rigid. Thus, it actually makes more sense to go from sql to a nosql, as you gain experience and discover the data access patterns. The advantage of nosql is not flexibility, that is actually its disadvantage! The advantage is rather its horizontal scalability. However, a decent sql server with competently designed schema will go a very long way.
- redwood 2y agoI think you have a very thoughtful take but believe it's a mistake to think of 'NoSQL' as a monolithic category.. There's a very wide spectrum from having an evolvable document oriented data model with evolvable strongly consistent secondary indexes, transactions, aggregations, and joins to simplistic key/value stores like DynamoDB and Cassandra that do force you into a very much waterfall posture that I think you are spot on in pointing out.
- evnix 2y agoI call it the "Build now, cry later" approach.
- shreddit 2y agoSecond that, i’ve built multiple services with MongoDB just to port them now (and probably for some time) to postgres.
- markus_zhang 2y agoJust curious why? I have never worked with NoSQL but I always envy the people who does that because they are a bit further away from business than us data warehouse modellers. They are usually our upstream, paid more and less hassle from business.
- irisgrunn 2y agoThat only makes sense if you wanted to store relational data in a NoSQL database (and that's not what Mongo is meant to do)
- threeseed 2y agoAnd if your business is okay with all of your data living in a single instance. Because PostgreSQL is unacceptably poor at HA/replication compared to MongoDB.
- spacebanana7 2y agoIs that really true these days? Setting up Postgres read replicas with automatic fail over across multiple machines is pretty trivial in the cloud with services like RDS, spanner etc. And although doing it in your own datacenter is still a big job it's far from impossible.
- sgarland 2y agoHuh? Replicas are easy, and hot standby nodes aren’t that hard either. There are also various active-active solutions if you need that.
- captn3m0 2y agoI've been building something using a JSON-blob inside SQLite using the Schema.org/Event schema. It's turning out okay.
- petalmind 2y agoUp to chapter 6 the method is completely database-independent. In chapter 7, a classic relational table approach is explained. You can choose a different approach if you want to use something like Cassandra, MongoDB or DynamoDB (or some other NoSQL solution). I am not an expert in all three, but I hope some day to write "alternative ending" for this tutorial and show how it can be applied in those environments. Whatever database you choose, you still must do all the work of logical modeling (first six chapters) for your application. If you don't do that explicitly, you would have just to do that implicitly, intertwined with physical design and thus inherently more confusing.
- LeonB 2y agoI found this to be a good introduction and a well-chosen domain to demonstrate modelling. The term “anchor” feels kind of weird to me, but the explanation is so concrete/grounded (like an actual anchor) that I guess it works well enough. The concept of defining the attributes via a question is solid, great way to get clarity quickly. Too often we jump to a minimal column/property name without defining what question we’re trying to answer, and thus not shaking loose any ambiguity in the mind of the customer(s).
- petalmind 2y agoThank you! > The term “anchor” feels kind of weird to me This term was inherited from Anchor Modeling (https://en.wikipedia.org/wiki/Anchor_modeling https://en.wikipedia.org/wiki/Anchor_modeling). "Entity" is pervasive, but I don't want to use this word because it may bring unnecessary baggage and assumptions. Also, this term is heavily overloaded: basically everything in CS is either an object or an entity, lol. > The concept of defining the attributes via a question is solid, great way to get clarity quickly. Thank you, this is an important confirmation for the validity of this approach.
- LeonB 2y agoI’ve been thinking about this since writing the gp comment yesterday and quite like the anchor terminology. Entity/object and “aggregate root” bring all sorts of complications/ambiguity. The example you gave of a non-anchor (price was it?) was helpful to clarify. Examples (and counter examples) are far easier for human brains to understand than “definitions”. so often the example domain for these sorts of exercise is “a shopping cart website” —- and I find shopping websites such a poor example, because the specifics of how the shopping experience should work at a small Website are very custom to what is being sold, to whom, and why etc. Other common domains when learning about databases — “a book lending library” (I found this a good domain when I was learning, but people’s experiences of libraries are very different now…) - a blog (posts, comments etc…) - but only fools like me write their own blog software now… (and or allow the public to comment? Yuck!) I’ve written scripts that query outlook appointments and found that their handing of recurring appointments was very odd! Writing a query that simple asks “what am I doing today?” is far from a trivial exercise, as a consequence. The choices and trade offs around storing every instance of a recurring appointment versus storing just the pattern plus the exceptions, has some pretty weird/nuanced implications. And I think the optimum approach really depends on the way I. Which the tool is used… which you don’t know until after the tool is used! (I guess that lends further proof that the most important aspect of software is that it’s open to being change in unexpected way… as any software worth writing will also need to be modified later.) Cheers! Keep doing good work!
- throwaway211 2y agoInteresting. How about edits, changes of time and location, who's signed up and to which revision.
- michaelmior 2y agoI think edits and changes of time were pretty well discussed (although certainly some details missing).
- lichtenberger 2y agoI once implemented the backend of a calendar and resource control for a low code platform. The control is highly customizable, with a lot of views to chose from, daily, monthly, yearly... but also resource views (you can book resources with custom groupings, by plugin, by the resource-ID, whatever...), define "plugins" on the data sources, what's the from- and to- columns, the title column, what's the resource (may be from a foreign key / 1:1 relationship or 1:N if it's from a "child" data source or from the same data source/table). Furthermore I've implemented different appointment series, to chose from (monthly, weekly (which weekdays), daily...), which column values should be copied. Also appointment conflicts (or only conflicts if they book the same resource). You could also configure buffers before and after appointments where no other appointment can be. That was a lot of fun and also challenge sometimes regarding time zones and summer/winter time in Europe and so on :-)
- canucker2016 2y agoTimezones will bend your mind, especially around the transition times. Assuming your timezone jumps forward one hour for daylight savings time and falls back one hour for transition to standard time... When your time skips forward one hour, your 1 hour event may now be displayed as spanning two hours - the second hour will not be reachable/does not exist. When your time falls back one hour, your 1 hour event may now show as spanning 2 hours or 0 hours. Timezones are a man-made construct so don't hardcode values cause things will change...
- 0xEF 2y agoAre you suggesting to just make the calendar in UTC with zero timezones? I agree that they are something we created, but we created them for better local organization. I work with customers globally and everyone uses them. This is a pretty ridiculous ask, in my opinion. Rather just focus on eliminating the Daylight Savings concept from the few localization that still use it, as they tend to cause the most confusion across timezones, especially when planning past an upcoming shift.
- canucker2016 2y agoI did not say that. Timezones are not carved in stone. Prepare for that. A location could go from a +1/-1 timezone as in most of USA/Canada to a fixed timezone with no transitions. There are various ways to adapt, but the user-friendly way involves a lot more work in the app, especially if the app thought that timezone data wouldn't change.
- Lutger 2y agoIndeed. There are multiple timezone changes each year, causing gaps in time. Also, not every timezone is a neat 1 hour increment, some are 15 minutes or worse. There are even people living in the same geographical location but in different timezones! Dealing with timezones will drive you mad, quickly.
- canucker2016 2y ago
- chandureddyvari 2y agoThis article about dealing with recurrence in applications was an eye-opener for me. Definitely recommend giving it a read. https://github.com/bmoeskau/Extensible/blob/master/recurrence-overview.md https://github.com/bmoeskau/Extensible/blob/master/recurrenc...
- Sander_Marechal 2y agoFunny how the original article's advice and your article oppose each other. - OP says to always store a timezone with each date, yours says to convert everything to UTC (I agree with OP) - OP says to generate database rows for each event, yours says to not do that (I agree with yours)
- fendy3002 2y ago> OP says to always store a timezone with each date Jon Skeet talked about this once. https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a-silver-bullet/ https://codeblog.jonskeet.uk/2019/03/27/storing-utc-is-not-a... What I take is convert everything to UTC is fine if it's historical data, even unix timestamp is fine. However for the future datetime, it's more complicated than that.
- deleted 2y ago[deleted]
- UglyToad 2y agoHaving built recurring stuff in the past (date based with no time component, luckily for me) I think you gain a lot of usability gains for generating a row for each occurrence of the event. Inevitably the user will come back and say "oh, I want it monthly except this specific instance" or if it's a time based event "this specific one should be half an hour later". You could just store the exceptions to the rule as their own data-structure but then you need to correlate the exception to the scheduler 'tick' and if they can edit the schedule, well, you're S.O.O.L either way but I think having concrete occurrences is potentially easier to recover from.
- bigbones 2y agoA random event from my calendar serializes to 740 bytes of iCalendar. An extremely busy calendar containing one event every 15 minutes 9am-5pm 7 days per week only contains 11680 entries for a full year, which works out to around 8MB. Would suggest instead of designing a schema at all, a calendar is a good example of a problem that might be far better implemented as a scan. Optimizing an iCalendar parser to traverse a range of dumped events at GB/sec-like throughputs would mean the above worst-case calendar could be scanned in single-digit milliseconds. Since optimizing a parser is a much simpler problem to solve once than changing or adding to a bad data model after it has many users, and that the very first task involving your new data model is probably to write an iCalendar importer/exporter anyway, I think this would be a really great trade off.
- canucker2016 2y agoiCalendar is designed as an interchange format not as a storage format for fast access of calendar data. The format shows its age - you can tell it was designed before XML/JSON were "hot". see https://en.wikipedia.org/wiki/ICalendar https://en.wikipedia.org/wiki/ICalendar
- rockwotj 2y agoIt’s like the email format - it’s the wild west trying to parse these things, it doesn’t help that date/time is already a tricky subject.
- LeonB 2y agoIn the Wild West things were easier — “Meet me at high noon!” Was never met with - “High noon? What time zone?”
- bigbones 2y agoIt is also a data model that amounts to a de facto standard across all real calendaring apps. The text serialization sure is ugly and who would want to touch it unless they had to, but that's just a parsing problem. The wiki page shows 14 component types, who is ever going to think of that many when designing a replacement schema? Why not just use the data model you already have 'for free'
- evnix 2y agoNicely done. This is one of those aspects which is not really touched on in most courses. Will try to get my company to get a few copies of your book for each of our team member.
- petalmind 2y ago> This is one of those aspects which is not really touched on in most courses. Thank you! As I was working on all of this, the more it seemed to me also that what you're saying is true. The goal of this approach is to quickly get a very concrete deliverable that you can confirm with the business people and then proceed with implementing in the specific database, using your favorite table design strategy.
- sensanaty 2y agoThe worst I have ever messed up during an interview was building a simple booking system that had to do recurring appointments. I never felt that lost and confused trying to accomplish something in code since my early university days lol To this day whenever I have to work with anything datetime related I dread it, it just does not click in my head for some reason
- tonnydourado 2y agoAsking a design question like "Design a booking system, but with recurring appointments" is like asking "Write a function to order a list of string, but it has to work with arbitrary UTF-8 strings, in any locale, respecting alphabetical order conventions". It's a deceptively simple question that gets impossibly hairy if you try to make it work in a real-world, general, way.
- dmd 2y ago(As someone who hires) the correct answer to any question involving datetimes is always "use a [good] library - do not attempt to reimplement all of human civilization from scratch".
- mattgreenrocks 2y agoDon’t feel bad about it. I was tasked with improving a calendar in a CMS to support a bunch more functions, and basically eaten alive by it. I had 8yrs of experience or so by then. It is deceptively hard and requires excellent data modeling skills.
- munchler 2y agoModeling a system is an under-appreciated skill. In a new domain, however, this should really start with an analysis of the entire problem so as to capture both the static structure of the system (i.e. class model) and its dynamic behavior (i.e. use cases). Jumping directly into a static database model tends to leave out the dynamic behavior. That might be OK in a simple CRUD app like this one, but could be a big mistake in more complex systems.
- apwheele 2y agoData scientist, and I have had a few examples of seemingly simple "how would you build a schema" job interview questions that I had a difficult time with on the spot. So last one I remember was how would you build a product table with coupons. Ok, so two tables right, no big deal. Well, we are going to need to keep a history right? So now I need to update and have datetimes for different products and coupons. And now I should think about how to do indexes on the tables, and gosh my join to get the discounted price is that a good way to do that? Most coupons only allow a person to use them once, how the hell am I going to implement that? They probably just wanted the simple product + coupon table, but let me spin on it for quite a while like a madman.
- blowski 2y agoI'd say this is exactly what the interviewers wanted. They're interested in how you break down the problem, the types of solutions you consider, your understanding of the trade-offs involved. For example, I interviewed somebody who was adamant they could prevent double-booking by polling an end-point and storing the state in Redux. Fantastic JavaScript skills, terrible knowledge of databases.
- nerdponx 2y agoI don't like these questions. Data warehouse schema design is out of scope for data science, it's data engineering. Yes we have to do a lot of ad-hoc data engineering along the way, but it's such a strange thing to interview for in lieu of the many possible data/math/stats skills and more directly relevant programming skills. It signals a lack of respect for division of labor and specialization, and that lack of respect will be visible in the form of a stretched inefficient team. To be clear, I think the ability to be your own data engineer is a great attribute as a data scientist. I just don't think it's reasonable to expect it: it's not part of the core job description.
- pg_bot 2y agoYou don't want to store two dates for an event. It's easier to store the start time of the event and a duration for the event. This will make the logic for updating your events simple. You can always calculate the end time of the event based off the start and duration.
- sleepyhead 2y agoThat would be problematic for database performance. You would have to calculate the duration on demand when querying.
- sgarland 2y agoTbf at Google / Apple scale this may not hold true, but an addition should not cause any noticeable stress to a DB as a generated column. Or do it in the app, of course.
- yencabulator 2y agoAnd now consider daylight savings time changes.
- skeeter2020 2y agoone of you is optimizing for transactional operations and the other reporting :)
- pg_bot 2y agoIn practice performance problems are a nonissue. Your API should consist of start time and duration because that is how people think about appointments and meetings. (An hour long meeting starting at noon) It's a pain to write a UI that updates two pieces of information when one piece of information changes. If you are truly worried about query performance you can denormalize the data before saving by storing start/end times as a datetime range field, but it still makes no sense to expose that in your API.
- 2y ago
- roland35 2y agoAll I want in Google calendar is a log of changes to the calendar itself. Please add this to the database!
- trwhite 2y agoYears ago I worked on a calendar application with recurrence. After lots of research I settled on using RRules to represent this, which I was very pleased with. That initial work was when I was at an agency. Later I joined the company full time and discovered to my amazement that a contractor from a different company had removed the RRules in favour of creating and destroying instances of events on the fly. It had no/little fault tolerance so sometimes the script (which did other things that would sometimes fail) would fail to create new events. You'd have monthly recurring events with missing months. I found it so frustrating that (after going through a lot of thought and research) that someone hadn't put anywhere near as much effort into removing mine. It took just a few weeks at that company to realise that the CEO expected the Engineering team to pump out features (that nobody used) at his will and, in the uncertainty of the job market, sadly I stayed there for 2 years. Unrelated footnote: After Googling them, it's really sad to see what are blatantly fake reviews by the CEO on Glassdoor all written in the same style with nothing bad to say. I (and a bunch of other people I know who worked there) hated him, but the silver lining is that I wrote some of my best essays there. The CTO was hopeless too.
- deleted 2y ago[deleted]
- deleted 2y ago[deleted]