Ironroot Craft Brewery ====================== Database schema: craftBrewery Platform: mariadb Tables: 9 Total rows: 5,604 ABOUT THE BUSINESS ------------------ Ironroot Craft Brewery is an independent craft brewery that produces a rotating lineup of ales, lagers, and seasonal specialties. It sells kegs and cases wholesale to bars, restaurants, bottle shops, hotels, and sports venues spread across multiple regional territories. The brewery also runs an on-site taproom where anyone can walk up and order pints, flights, or growlers directly. With a catalog of over 260 distinct beer recipes and more than a thousand batches brewed, Ironroot is a mid-sized regional operation with both a B2B wholesale arm and a direct consumer-facing taproom. HOW THE BUSINESS OPERATES ------------------------- On the production side, the brewery takes a beer recipe and brews it into a physical batch, which moves through stages — fermenting, conditioning, packaging, and eventually depletion as it sells out. Once packaged, that beer becomes available for sale through two channels. In the wholesale channel, a sales rep manages accounts in their territory; when a bar or bottle shop is ready to reorder, they place a wholesale order that lists exactly which beers, in which package format, and at what quantity they want delivered. In the taproom channel, staff ring up individual transactions whenever a customer orders a pint or growler at the bar, and each transaction records which beer was served and how. WHAT EACH TABLE HOLDS --------------------- styles (12 rows) A short reference list of the broad beer style categories Ironroot uses to classify its recipes — things like IPA, Stout, or Lager. Every beer in the catalog is assigned to exactly one style from this list. beers (260 rows) One row for every distinct beer recipe the brewery has ever created, including beers that are currently on tap, beers that rotate in seasonally, and recipes that have been retired. This is the central product catalog that everything else references. batches (1,200 rows) One row for every physical brewing run the brewery has executed. Because a popular recipe might be brewed dozens of times, this table tracks each individual batch separately — when it was brewed, how many litres were produced, and where it currently stands in the production process. territories (12 rows) A short reference list of the geographic sales territories the brewery distributes into. Each territory has a dedicated sales rep who owns the relationships with wholesale accounts in that area. accounts (260 rows) One row for every wholesale customer the brewery sells to — bars, restaurants, bottle shops, hotels, and sports venues. Each account belongs to one sales territory and is served by that territory's sales rep. orders (1,200 rows) One row for every wholesale order placed by an account. An order captures when it was placed, when delivery is expected, and its current fulfillment status from initial receipt through to delivery or cancellation. The individual beers and quantities within that order are recorded separately in orderLines. orderLines (1,200 rows) One row for each distinct beer-and-package combination within a wholesale order. A single order from a bar might include a half keg of one beer and two cases of another, which would appear as two separate rows here. taproomSales (1,200 rows) One row for every transaction rung up at the on-site taproom. Rather than wholesale cases or kegs, these transactions capture individual servings — pints, half pints, flights, growler fills, and crowlers — sold directly to visitors at the brewery. beerTags (260 rows) One row for every flavor or ingredient tag applied to a beer. Because a single beer can carry multiple tags (for example, both Hoppy and Citrus) and the same tag applies to many beers, this table links the two together, enabling searches and groupings by flavor profile. HOW THE TABLES RELATE --------------------- Every beer recipe belongs to one broad style category, and each recipe can be brewed into many separate batches over time. On the wholesale side, accounts are grouped into territories, and when an account places an order, that order breaks down into individual line items — one per beer and package type combination. On the taproom side, each sales transaction ties back directly to a specific beer recipe, recording exactly which beer was served. Beers can carry multiple flavor tags, and the same tag can appear on many different beers, so the beerTags table connects those two sides together. - Each beers row belongs to one styles, through beers.styleId → styles.styleId. - Each batches row belongs to one beers, through batches.beerId → beers.beerId. - Each accounts row belongs to one territories, through accounts.territoryId → territories.territoryId. - Each orders row belongs to one accounts, through orders.accountId → accounts.accountId. - Each orderLines row belongs to one orders, through orderLines.orderId → orders.orderId. - Each orderLines row belongs to one beers, through orderLines.beerId → beers.beerId. - Each taproomSales row belongs to one beers, through taproomSales.beerId → beers.beerId. - beerTags resolves a many-to-many relationship: each row pairs one beers with one other party via beerTags.beerId. DATA DICTIONARY --------------- styles styleId INT e.g. 1 Unique identifier for each styles row. styleName VARCHAR(60) e.g. Porter The name of the beer style family. description VARCHAR(255) e.g. Fulfilled from the service de… A short description of the style's flavor profile and characteristics. beers beerId INT e.g. 1 Unique identifier for each beers row. beerName VARCHAR(120) e.g. Returned listing The marketed name of the beer, e.g. 'Redrock Amber'. styleId INT e.g. 6 The broad style family this beer belongs to. abv DECIMAL(12,1) e.g. 11.2 Alcohol by volume expressed as a percentage, indicating the strength of the beer recipe. ibu INT e.g. 45 International Bitterness Units — a numeric scale measuring how bitter the beer tastes; higher numbers mean more bitterness. isActive TINYINT(1) e.g. 0 Whether this recipe is currently being brewed and sold. Retired or discontinued recipes remain in the catalog but are flagged inactive. batches batchId INT e.g. 1 Unique identifier for each batches row. beerId INT e.g. 243 The beer recipe brewed in this batch. batchCode VARCHAR(30) e.g. BT-7153-AC A short human-readable identifier assigned by the brewery to track and reference a specific batch during production, such as on tank labels or production logs. brewDate DATE e.g. 2024-09-02 The date brewing began for this batch. volumeLitres DECIMAL(12,0) e.g. 1103.33 The total volume of beer produced in this batch, measured in litres. status VARCHAR(60) e.g. Fermenting Where this batch currently sits in the production pipeline — Fermenting and Conditioning mean it is not yet ready, Packaged means it is ready to sell, and Depleted means it has been fully sold or consumed. territories territoryId INT e.g. 1 Unique identifier for each territories row. territoryName VARCHAR(60) e.g. North Metro The name of the sales territory. salesRep VARCHAR(100) e.g. Oona Ueda The full name of the sales representative responsible for maintaining relationships with wholesale accounts in this territory. accounts accountId INT e.g. 1 Unique identifier for each accounts row. accountName VARCHAR(120) e.g. Beacon Systems The trading name of the bar, restaurant, or bottle shop. territoryId INT e.g. 10 The sales territory this account falls within. city VARCHAR(60) e.g. Naperville City where the account is located. phone VARCHAR(20) e.g. (557) 913-5919 Primary contact phone number for the account. accountType VARCHAR(60) e.g. Bottle Shop The type of business the wholesale customer operates, such as Bar, Restaurant, or Bottle Shop. This helps the brewery segment and analyze its customer base. orders orderId INT e.g. 1 Unique identifier for each orders row. accountId INT e.g. 3 The wholesale account that placed this order. orderDate DATE e.g. 2023-06-04 The date the wholesale account placed the order with the brewery. deliveryDate DATE e.g. 2023-11-03 The date the order is scheduled to be — or was actually — delivered to the account. status VARCHAR(60) e.g. Shipped The current fulfillment stage of the order, moving from Pending through Confirmed, Shipped, and Delivered, or Cancelled if the order did not proceed. orderLines orderLineId INT e.g. 1 Unique identifier for each orderLines row. orderId INT e.g. 80 The parent wholesale order this line belongs to. beerId INT e.g. 39 The beer being ordered on this line. packageType VARCHAR(60) e.g. Half Keg The physical format in which the beer is being ordered — for example, a Half Keg for draught service or a 24-Case of cans for retail shelves. quantity INT e.g. 2 Number of units (kegs or cases) ordered. unitPrice DECIMAL(12,2) e.g. 136.74 The price charged per single unit of the specified package type at the time the order was placed. lineTotal DECIMAL(12,2) e.g. 273.48 The total charge for this line, calculated by multiplying the quantity ordered by the unit price. taproomSales taproomSaleId INT e.g. 1 Unique identifier for each taproomSales row. beerId INT e.g. 1 The beer sold in this taproom transaction. saleDate DATETIME e.g. 2023-11-14 12:50:00 The exact date and time the taproom transaction was completed, recorded to the minute so the brewery can analyze traffic patterns throughout the day. servingType VARCHAR(60) e.g. Pint The format in which the beer was served at the taproom — for example, a Pint poured at the bar or a Growler Fill for a customer taking beer home. quantity INT e.g. 1 Number of servings sold in this transaction. saleAmount DECIMAL(12,2) e.g. 9.29 The total dollar amount collected from the customer for this taproom transaction. beerTags beerTagId INT e.g. 1 Unique identifier for each beerTags row. beerId INT e.g. 2 The beer receiving this tag. tagName VARCHAR(60) e.g. Malty A single descriptive label applied to the beer, drawn from a defined set of flavor and ingredient descriptors such as Hoppy, Citrus, or Smoky. PROVENANCE ---------- Every value in this dataset is synthetic. No real person, company, or record is represented. Generated by the Agoge dataset generator 1.0.0 with seed 23. The same brief and seed reproduce this data exactly.