• Physical Data Modeling
  • Hotel Physical Data Model
  • Reservation Physical Data Model

    Physical Data Modeling

    Once you have a logical data model defined, creating the physical model is a relatively simple process. CQL data type <data-types>, including the basic types, collections, and user-defined types. You may identify additional user-defined types that can be created to simplify your design. After you’ve assigned data types, you analyze the model by performing size calculations and testing out how the model works. You may make some adjustments based on your findings. Once again let’s cover the data modeling process in more detail by working through an example. Before getting started, let’s look at a few additions to the Chebotko notation for physical data models. To draw physical models, you need to be able to add the typing information for each column. This figure shows the addition of a type for each column in a sample table. image The figure includes a designation of the keyspace containing each table and visual cues for columns represented using collections and user-defined types. Note the designation of static columns and secondary index columns. There is no restriction on assigning these as part of a logical model, but they are typically more of a physical data modeling concern.

    Hotel Physical Data Model

    hotel keyspace to contain tables for hotel and availability data, and a reservation keyspace to contain tables for reservation and guest data. In a real system, you might divide the tables across even more keyspaces in order to separate concerns. hotels table, use Cassandra’s text type to represent the hotel’s id. For the address, create an address user defined type. Use the text type to represent the phone number, as there is considerable variance in the formatting of numbers between countries. uuid type for attributes such as the hotel_id, this document uses mostly text attributes as identifiers, to keep the samples simple and readable. For example, a common convention in the hospitality industry is to reference properties by short codes like “AZ123” or “NY229”. This example uses these values for hotel_ids, while acknowledging they are not necessarily globally unique. uuids as references in tables representing other entities. This helps to minimize coupling between different entity types. This may prove especially effective if you are using a microservice architectural style for your application, in which there are separate services responsible for each entity type. As you work to create physical representations of various tables in the logical hotel data model, you use the same approach. The resulting design is shown in this figure: image address type is also included in the design. It is designated with an asterisk to denote that it is a user-defined type, and has no primary key columns identified. This type is used in the hotels and hotels_by_poi tables. address user-defined type. This can reduce complexity in the design. address in the reservation keyspace defined below design, you’ll have to declare it again. This is just one of the many trade-offs you have to make in data model design.

    Reservation Physical Data Model

    Now, let’s examine reservation tables in the design. Remember that the logical model contained three denormalized tables to support queries for reservations by confirmation number, guest, and hotel and date. For the first iteration of your physical data model design, assume you’re going to manage this denormalization manually. Note that this design could be revised to use Cassandra’s (experimental) materialized view feature. image address type is reproduced in this keyspace and guest_id is modeled as a uuid type in all of the tables. Material adapted from Cassandra, The Definitive Guide. Published by O’Reilly Media, Inc. Copyright © 2020 Jeff Carpenter, Eben Hewitt. All rights reserved. Used with permission.