LITA Post – Diagrams Made Easy with LucidChart

This post was originally published on Library & Information Technology Association (LITA) Blog

For the past year, across four different classes and countless bars, I have worked on an idea that is quickly becoming my go-to project for any Master of Information Science assignment; the Archivist Beer Vault (ABV) database. At first it was easy to explain the contents: BEER! After incorporating more than one entity the explanation grew a bit murky:

ME: So remember my beer database? Well now it includes information on the brewery, style AND contains fictional store transactions
WIFE: Good for you honey.
ME: Yeah unfortunately that means I need to add a few transitive prop… I lost your attention after beer, didn’t I?

Which is a fair reaction since trying to describe the intricacies of abstract ideas such as entity relationship diagrams require clear-cut visuals. However, drawing these diagrams usually requires either expensive programs like Microsoft Visio (student rate $269) or underwhelming experiences of freeware. Enter Lucidchart, an easy to use and relatively inexpensive diagram solution.

The website starts off users with a few templates to modify from 16 categories, such as Flowchart and Entity Relationship (ERD),  or you can opt for a blank canvas. I prefer selecting the Blank (Name of Diagram) option as it clears the field of any unneeded shapes and preselects useful shapes.

LucidChart-Shapes-Flowchart
Preselected shapes for a Blank Flowchart document

 

While these shapes should be more than enough for standard diagrams, you are also free to mix and match shapes, such as using flowchart shapes for your wireframe diagram. This is especially helpful when creating high fidelity wireframes that require end product level of detail.

Once you have selected your template it is easy to begin your drawing by dragging the desired shapes onto the canvas. Manipulating shapes and adding text overlays is straightforward, you merely click the edge of the boxes of the shape you want and adjust the size of it, which can either be done manually or set to a specific pixel size. Using the program is akin to having access to Photoshop’s powerful image manipulation tools but in a streamlined user-friendly UI. Most users can get by with just the basic options but for advanced users there are settings to adjust your page size and orientation, add layers, revision history, theme colors, adjust image size, and advanced text options. The frequently updated UI adds user requested features and contains tutorials within the diagram menu.

Adjust shapes by clicking on corners or select Metrics to adjust to specific size.
Adjust shapes by clicking on corners or select Metrics to adjust to specific size.

It also contains intuitive features such as converting lines that connect entities into cardinality notations with pulldown options to switch to  the desired notation style. This feature is not only practical but can also help with development. Getting back to the ABV, as I drew the entity structures and their cardinalities I realized I needed to add a few more transitive entities and normalize some of the relationships as I had a highly undesirable many-to-many relationship between my purchase table and items. As you can see below, the ABV’s ERD makes the complex relationships much more accessible to new users.

BEHOLD! BEER!
BEHOLD! BEER!

It was easy to move tables around as LucidChart kept the connections on a nice grid pattern, which I could also easily override if need be. This powerful flexibility lead to a clean deliverable for my term project. The positive experience I had creating this ERD lead me to try out the program for a more complex task, creating wireframes for a website redesign project in my Information Architecture class.

Tasked with redesigning a website that uses dated menu and page structures, our project required the creation of low, medium, and high fidelity wireframes. These wireframes present a vision for the website redesign with each type adding another layer of detail. In low fidelity wireframes, image placeholders are used and the only visible text are high level menu items while dummy text fills the rest. Thankfully LucidChart’s wireframe shapes contained the exact shapes we needed. Text options are limited but it did contain one of the fonts from our CSS font family property. Once we reached the high fidelity phase it was easy to import our custom images and seamlessly add them to our diagram.

Low, Medium, and High fidelity wireframes of a redesign project.
Low, Medium, and High fidelity wireframes of a redesign project.

Once again LucidChart provided a high quality deliverable that impressed my peers and professor. With these wireframes I was able to design the finished product. With LucidChart’s focus on IT/engineering, product management & design, and business, you can find a vast amount of shapes and templates for most of your diagram needs such as Android mockups, flowcharts,  Venn diagrams and even circuit diagrams. There are a few more perks about LucidChart and a few lows.

PERKS

  • Free… sort of: For single users there are three levels of pricing; Free, Basic $3.33/month (paid annually), and Pro $8.33/month (paid annually). Each level adds just a bit more functionality than the last. The free account will get you up and running with basic shapes but limited to 60 per document. Not too bad if are you creating simple ERDs. Require more than 60 objects or an active line to their support? Consider upgrading to Basic. Need to create wireframes? Well you’ll need a Pro account for that.
    Thankfully, they are actively seeking to convert Visio users by offering promotional pricing for certain users. For instance, university students and faculty can follow the instructions on this page  to request a free upgrade to a Pro account. Other promotions include 50% off for nonprofits and free upgrades for teachers. Check out this page to see if you qualify for a free or discounted Pro account. I can only speak for the Education account that adds not only the Pro features but also the Google Apps integration normally found under Team accounts.
  • Easy collaboration… for a price: As seen in the figure below, users can reply, resolve or reassign comments on any aspect of the diagram.
    LucidChart-Comments
    Comments example

    All account levels include these basic functions. However, a revision history that tracks edits made by collaborators requires a Pro account. Moreover, sharing custom templates and shapes are functions reserved for Team account users, which starts at $21/month for 5 users.
    One final note: each collaborator is tied to their own account limitations which means free account users may only use 60 shapes even if they are working on a diagram created by a Pro account.

  • Chrome app: The Chrome app converts the website into a nice desktop application that is available offline. Once you are back online the application instantly syncs to their cloud servers. The app is fully featured and responds quicker than working on the website. Using the app is a much more immersive experience than the website.

LOWS

  • Pricing for non-students: As you can see by now LucidChart has an aggressive pricing plan. The Free account is enough for most users to decide if they want to create diagrams that involve more than 60 shapes. It is a bit disappointing to see that the Basic account only adds unlimited shapes and email support. Furthermore, wireframes and mockups are locked up behind the Pro level. Most of these Pro features should really fall under Basic. Still, the $99 annual price for a LucidChart Pro account is far less than Visio, which starts at $299 for non-students.
  • Chrome app stability: For the most part the website has been a flawless experience, the same cannot be said for their Chrome app. There have been times where the application crashes to desktop, the constant syncing did save all of my work, or some shapes becoming unresponsive. There is also an ongoing bug that keeps showing me deleted documents, whichdonotappear on the website.
    LucidChart-Ghosts
    Icons in grey were deleted months ago but still show up in the Chrome App

    None of these knocks against the app have prevented me from using it but it is worth mentioning that the app is a work in progress and can feel like a lower priority for the company.

Don’t just take my word for it, you can try out a demo on their website that contains most of the Pro features. Are there any projects you can see yourself using LucidChart? Have a Visio alternative to share? I’d love to hear about any experiences other users have had.

Archivist’s Beer Vault Using MS Access

PROJECT

The Archivist’s Beer Vault (ABV) is a database that I have worked on since my second semester at UNT. Initially it was just a simple beer database built using a fairly low tech software. For my term project in SLIS 5707 Data Modeling for Information Professionals we were tasked to devise a multiple entity database and create it using Microsoft Access. Converting the ABV into a multi entity database was a bit perplexing but once I figured out the entity-relationship diagram it was smooth sailing. This was my first forray into Access and I was impressed with the flexibility and power of the program. Knowing some SQL commands that I used for my Team ASPCA Questionnaire project definitely helped with the query and reports aspects of it. I was able to finish with enough time to implement a great looking UI for the database. Here is my ABV built by Access.

Main Menu
Main Menu

 

OVERVIEW

The Archivist’s Beer Company (ABC), located in Pasadena, California, is the largest craft beer specialty store in the state. The store is stocked with well over two thousand individual craft beers from across the United States. The store sees 300 customers in any given day, of which 15-25 are new customers. A new soccer stadium down the street is set to open within a year and business is expected to boom. The database, Archivist’s Beer Vault (ABV), grew out of the need to keep track of the large collection or beers, as well as a rapidly expanding user base. The ABV houses not only beer and customer information but also distributor profiles and customer purchase history.


 

USER REQUIREMENTS

With the high amount of beers in stock, the store is not easy to browse. Instead of having to navigate aimlessly down endless rows of bottles and cans with only labels to go off of, customers use the ABV, through the ABC website and in-store kiosks, to smartly drill down by important beer attributes, such as the International Bitterness Units (IBU) or seasonal availability, which are not necessarily found directly on the bottle. Browsing beers that share beer style allows customers to see what other beers the store has that fits their preferences. In effect, the database exists to aid customers find the beer they need and as a discovery tool.

On the staff side, the ABV fills multiple needs that can only be achieved with a database. For example, the store’s layout is built around beer style instead of the usual brewery name organization scheme found in rival stores. That is to say American Pale Ales are right next to American Wheat Pale Ales but far away from Doppelbock. Staff must know what style a beer is set to and find the appropriate stocking location. The store has a policy of awarding contracts of one brewery to one distributor, the ABV allows management to ensure that the policy is enforced. The ABV also allows marketing staff to monitor customer purchase trends to adjust marketing campaigns to highlight underperforming beers.


 

BUSINESS RULES

  • A customer may have zero or many orders.
  • A customer has only one favorite style.
  • A style may have zero or many beers.
  • A style may be the favorite of zero or many customers.
  • A purchase order is submitted by one customer.
  • A purchase order has only one customer.
  • A purchase order contains one or many beers.
  • A beer is listed only once per purchase.
  • A beer has only one brewery.
  • A brewery has only one distributor.
  • A brewery may have zero or many beers.
  • A distributor may distribute zero or many breweries.

MAJOR ENTITIES

CUSTOMER

This table contains information related to ABC customers. It contains the customer’s rewards program unique ID, contact information and favorite beer style.

BEER

This table contains individual beer details. It stores the beer Universal Product Code (UPC), name, brewery, assigned style, and vitals.

BREWERY

This table contains information regarding a brewery company. It stores the brewery ID, name, location and distributor.

STYLE

This table covers beer style details. It stores the style name, country of origin, suggested serving temperature, recommended glassware, best food pairing and shelving location.

PURCHASE

This table maintains records of purchases. It stores the purchase ID, customer ID, date of transaction and total.

DISTRIBUTOR

This table contains information related to ABC distributors. It contains the distributor ID, location and contact information.


ENTITY-RELATIONSHIP DIAGRAM

ERD
Entity Relationship Diagram

 


 

SAMPLE QUERIES

  • How many fall seasonal beers does the store carry?

SELECT COUNT(BEER_Availability)  AS [Number of beers AVAILABLE]

FROM BEER

WHERE (BEER_Availability = “Fall”)

GROUP BY BEER_Availability

ORDER BY COUNT (BEER_Availability) DESC;

  • Which Oregon breweries are represented in the store?

SELECT Brewery_name

FROM Brewery

WHERE Brewery_AState = “OR”

ORDER BY Brewery_AState ASC;

  • Which distributors supply beers from Texas?

SELECT Distributor.Distributor_ID, Distributor.Distributor_CompanyName, Distributor.Distributor_Contact, Brewery.Brewery_AState

FROM Distributor, Brewery

WHERE Brewery_AState = “TX”

AND Distributor.Distributor_ID = Brewery.Distributor_ID

ORDER BY Brewery_AState ASC;

  • How many customers love Stouts (Style_ID =2)?

SELECT COUNT(Style_ID) AS [Number of Stout Lovers]

FROM Customer

WHERE (Style_ID = 2)

GROUP BY Style_ID

ORDER BY ‘Number of Stout Lovers’ DESC;


 

SCREENSHOTS

Using various options in MS Access I was able to create an interface for the ABV.

This slideshow requires JavaScript.

Team ASPCA Half Marathon Questionnaire

PROJECT

For this project we were tasked with designing a survey in PHP that can add, edit and query an SQL database. Since I am a huge supporter of Team ASPCA I decided to create a survey for race participants in a fictional race. What follows is part of the write up for the project or you can just jump right into the survey  to check it out for yourself.

CLICK IMAGE FOR SURVEY.
CLICK IMAGE FOR SURVEY.

 INTRODUCTION

The TEAM ASPCA Half Marathon is a race event held on the last week of every October. Over 5,000 people participate in the three races; a 5K, 10K and half marathon. Each race has as a different course but finish and start at the same place. The first two events have been marred by reviews from Half Marathon runners complaining about the lack of support in the final stretch as runners from the shorter distances have used up the water and food. The event, in its third iteration, has become a marquee event for the ASPCA and management would like to improve the race to attract a larger crowd. A survey sent out shortly after the race can provide insight as to what the racers liked and disliked about the event.


SURVEY

Structure

First, the participant is presented with a brief survey covering unidentifiable personal information, such as location and race experience. The next page is the race survey that covers four vital aspects of the race: the pre-race, course, post-race, and the event overall. Each section is clearly marked and contains a textbox for users to add comments, questions or concerns. Every question but the comments boxes are required. After the race survey section the user is presented with a summary of their entries and a button to submit their responses. Finally, the user is thanked for their feedback and provided with a coupon for registering to next year’s event.

Questions

Participant Survey

  1. What is your age?
  2. What running team are you a part of?
  3. Where are you located? (Asks for City and State)
  4. How many races have you participated in?
  5. Is this your first time running one of our events?
  6. Did you fundraise?
  7. Which race did you participate in?

Race Survey

  1. Pre-Race
    1. Was there enough prerace communication?
    2. How did you keep up date about the race?
    3. Did you attend the race kickoff party?
    4. Which expo did you attend?
    5. How would you rate the expo?
    6. How would you rate the starting line area?
    7. OPTIONAL: Any comments about prerace events?
  2. Course
    1. How would you rate the course support?
    2. Who was your favorite cheer squad?
    3. Overall, how would you rate the course?
    4. OPTIONAL: Any comments about the course?
  3. Post-Race
    1. Did you attend the post-race festival?
    2. Was there enough post-race support?
    3. How would you rate the post-race area?
    4. OPTIONAL: Any comments about the post-race area?
  4. Overall
    1. Overall, how would you rate the event?
    2. OPTIONAL: Any comments about the event?

Justification

The participant section of the survey allows the race managers to capture some vital information from their users that allow them to establish user groups in reports, while still maintaining user privacy to allow for candid feedback. Each question in the race section covers different aspects from throughout the event experience. Knowing which communication channels worked can allow strategic marketing of future events. With past problems of spotty post-race support, the survey also allows managers to track if the issue is ongoing. Lastly, providing a comment box for each section allows users to focus their comments on particular aspects of the race.

 


DATABASE DESIGN

The database contains five entities, housed in a separate table:  the event, expo, participant, race and the opinion. Every field but the comment fields are required.

  • The Event table contains information of the iteration of the event. That is to say, the First Annual event has an ID of 1, the Second Annual has an ID of 2, and the Third Annual (this year) is 3. Every year a new row will be created to contain the information of the event.
  • The Expo table contains information of the expo of the event. There are three locations possible for the expo: Friday pick up at Run With Us, Saturday pick up at the Pasadena Convention Center and Sunday pick up at the race start.  There are only three packet ups allowed for the event.
  • The Participant table holds the personal information provided by the survey respondent
  • The Race table contains information on the three races held during the event. There is the 5K Presented by Run with Us, Race for the Rescues 10K, and the Team ASPCA Half Marathon.
  • Finally, the Opinion table holds the race feedback provided by the survey participant. All of the entities are tied together through this entity.
Entity Relationship Diagram
Entity Relationship Diagram

WEBSITE

Sitemap

TEAM-ASPCA-SiteMap
Site map of survey site

Functions

The welcome page explains the purpose of the survey to the participant and collects their personal information. A “On to Race Survey” button links through to the next page, project.php. This page stores the information from the previous page and collects information on the race, which is stored in the opinion table. The user hits the “Review Feedback” button that links to project2.php where they are presented with the information provided in the previous two pages. The user then hits “SUBMIT Feedback” to lead to project3.php that processes all of the information gathered in the survey and inserts it into our SQL database, once the transmission is complete the user is thanked for their time, presented with a coupon and linked back to the survey home page.

Link

CLICK IMAGE FOR SURVEY.
CLICK IMAGE FOR SURVEY.