Data Modeling

In order to convert a grocery store’s process into an ERD and a database schema, I used LucidChart and Vertabelo to create these structures.

Entity-Relationship Diagram

To begin, I created the customer, which has the attributes of name, address, and phone number, all used for customer identification. Many customers can then place many different orders at this store, so I applied the crows feet at both ends the arrows between them.

Orders contain the time and date of fulfillment for the order, as well as the type of order (pickup/delivery), and the special instructions requested by the customer. Another attribute is the finalized attribute, which functions more like a boolean asking whether or not the order is finalized.

The last entity is the Item entity. The relationship between the order and the item is that one order can contain multiple items, which is why there is a singular dash near the order end and crow’s feet near the item end. The item contains many attributes for an item in an order. These attributes include the name, price, description, manufacturer and quantity of the ordered items. Additionally, there is another boolean-like method as seen in Order entity.

Database Schema

When compared to the diagram, the schema is relatively similar.

The customer table also contains a customer’s name, address and phone number, but it also contains a field for a customer ID, which is the primary way of referring to a customer.

The order table is similar to the customer tab in relationship to the diagram. It gets an order ID that is used as the primary key. The difference is that an order will take in the customer’s ID, given that every order must be tied to a customer.

The item table also functions like it does in the ERD. Similarly, it brings in the ID of the order that it is tied to, and is referenced by its own ID. The primary difference one can see here is that the graph introduces another table to handle substituions, by taking in an item’s ID and storing another ID that references a possible substitution.

Evaluation

I believe this is a proper way to represent everything, the only thing I worry about is the substitution table in the schema. I feel like it may need to import more than just the Order’s item, but I’m not entirely sure what. I also worry if there are other parts of the table that need to abstracted a bit. I don’t see very many issues that could arise from implementing a model like the one here. I think that it would accurately represent the flow of online grocery shopping.