Schema Revamp

We're going to do a minor update to the schema and then implement a new deduplication and normalization approach.

I used Deep Research to help make the decision here - given its ultimately subjective - and I'm happy with the recs. Its one direction that I had previously implemented, and after all the iteration/trial and error, it feels right.

This isn't 1:1 but the information contained is good guidance. Particularly we're adding parent to Brand (done in schema, #237), ABV on bottles (#245). Distilleries will continue to be an []Entity. We'll also not be tracking bottle size as its not totally relevant to the goals.

I have a second prompt built that should do an ok job of taking a label (e.g. from a store) and extracting it into the right components.

The last thing here - we'll have a display name (currently fullName) that combines all the components to still create an absolute unique canonical name, but it may not be commonly used.

--

This is via OpenAI Deep Research:

Key entities include Brand, Distillery, Whiskey (Release), Whiskey Series (for grouping editions), Whiskey Type, Cask Type, and user-specific tables for tracking collections. Each whiskey entry stores attributes such as distillery, whiskey type, age, ABV, bottler/brand, and cask maturation details​, providing a comprehensive profile. Below is the structured schema with tables, their attributes, and the rules for normalization and deduplication:

Tables and Key Attributes

Brand

Represents the brand or bottler under which a whiskey is released (often the distillery for official bottlings, or an independent bottler for third-party releases). The brand name is the primary identifier on a whiskey label​.

BrandID (PK) – Unique identifier for the brand.
Name – Official brand name (e.g. Glenfiddich, Johnnie Walker, Signatory Vintage).
Owner/Company – Optional: Parent company or group (for informational purposes).
Country – Optional: Country of the brand’s origin or main operation (if relevant for labeling).

Normalization: Brands are stored once in this table and referenced by ID elsewhere, ensuring consistent naming and spelling for each brand across all entries. For example, “The Glenlivet” will always be labeled exactly as such by linking to its BrandID, preventing variants or typos from appearing in bottle entries.

Distillery

Represents the actual distillery where the whiskey was produced (important for single malts and distillery-specific releases). In many cases the Brand and Distillery are the same for official distillery bottlings, but they can differ for independent bottlings or blended brands.

DistilleryID (PK) – Unique identifier for the distillery.
Name – Name of the distillery (e.g. Glenfiddich Distillery, Jim Beam).
Country – Country where the distillery is located (e.g. Scotland, USA).
Region – Optional: Region of production (e.g. Speyside, Islay, Kentucky) for more granular classification.

Usage: The Whiskey table references a DistilleryID when the product comes from a specific distillery. For single malts, this is the producing distillery; for blends or multi-distillery products, this field can be left NULL or use a special entry (e.g. “Multiple Distilleries”) since multiple sources are involved. Storing distilleries in a separate table enforces normalization – each distillery’s name and info is maintained in one place, avoiding duplication if many bottles come from the same distillery​.

Whiskey (Release)

This is the central table detailing each unique whiskey bottling or release. Each row represents a specific whiskey product edition – identified by its combination of brand, name, age, year, cask, etc. For example, “Ardbeg Supernova 2019 Edition” would be one entry, and “Ardbeg Supernova 2015 Edition” another, each tied to the same series group. Key attributes:

WhiskeyID (PK) – Unique ID for the whiskey entry.
BrandID (FK to Brand) – The brand or bottler who released the whiskey. This links the whiskey to its brand (e.g. an official distillery bottling or independent bottler brand).
DistilleryID (FK to Distillery, nullable) – The distillery of origin. For independent bottlings, this is the producing distillery (e.g. a Signatory bottling of Laphroaig would have Brand = Signatory, Distillery = Laphroaig). For blends or generic brand releases, this may be NULL or not applicable.
SeriesID (FK to WhiskeySeries, nullable) – Grouping identifier if this release is part of a named series or line that has multiple editions. Releases that recur annually or in batches share a Series. (See WhiskeySeries below.)
Name/Expression – The specific product name or expression. This field distinguishes the whiskey if the brand alone is not specific. For example, for Glenfiddich 19 “Age of Discovery – Red Wine”, the Name could be “Age of Discovery – Red Wine”. For a core product like Glenlivet 12, the Name might simply be the brand’s common expression name (or could be left generic if the age covers it). If the whiskey is part of a series, this might hold the edition name or distinguishing title (e.g. “Red Wine Finish” as in the example). If it’s a one-off or core bottle, this can be the product name or a descriptor like “Sherry Cask” if needed.
TypeID (FK to WhiskeyType) – The category of whiskey. e.g. Single Malt, Blended Malt, Blended Scotch, Bourbon, Rye, Irish Whiskey, etc. This categorization helps users filter and understand the style.
StatedAge – The age statement in years (integer), if the bottle has one. This is the number of years the youngest spirit in the bottle was aged. If the whiskey is “NAS” (No Age Statement), this field is NULL. (No age statement usually implies at least the legal minimum age, but the database will not fill an age in that case​.)
VintageYear – Optional: The year of distillation or the start of aging (for vintage releases). If a whiskey is identified by a vintage (e.g. distilled in 1998), that year is recorded here.
ReleaseYear – Optional: The year the whiskey was bottled or released. Particularly useful for annual releases or batches that come out in a specific year (e.g. a 2021 special edition). This, together with VintageYear (if present) and Age, helps differentiate releases. For example, a whiskey might be distilled in 2000 (VintageYear) and bottled in 2020 (ReleaseYear) with a 20-year age statement.
ABV – Alcohol by volume as a percentage (e.g. 40, 46, 53.8). This can be a decimal to accommodate half percentages. ABV is important for differentiating standard vs cask strength releases.
CaskType (FK to CaskType, or text) – The primary type of cask used in maturation or finishing. Common examples: Bourbon cask, Sherry (Oloroso/PX) cask, Port pipe, Wine barrique, etc. This field can record the wood or previous contents that seasoned the cask (e.g. ex-Bourbon, ex-Sherry) which often differentiates one release from another. (Multiple cask finishes can be noted if applicable in a composite way or via a link table, but a single field for the dominant cask is usually sufficient for identification.)
Edition/Batch – Optional: A freeform field or code for batch or edition designation. This can store identifiers like “Batch #3”, “Cask 1201”, or names like “Committee Release”. Use this if the series/year alone isn’t enough to identify the bottle. For instance, some distilleries release multiple batches in one year; e.g., Elijah Craig Barrel Proof Batch A121. In that case, ReleaseYear = 2021, and Edition = “A121” captures the batch code.
Bottler – Derived through Brand: A flag or info to indicate if it’s an Original Bottling (OB) by the distillery or an Independent Bottling (IB). This can be derived by comparing Brand and Distillery: if Brand = Distillery’s official name, it’s an OB; otherwise it’s an IB by that Brand. (Alternatively, a boolean field or enum can explicitly mark independent bottlings.) This distinction is important since the bottle label will often note the bottler​.

Other Attributes – Optional fields for further differentiation:

BottleSize (e.g. 700ml, 750ml) if the database needs to track volume – some releases have multiple sizes which might be separate entries (to avoid confusion, volume can be part of the entry if needed).
ChillFiltered (Yes/No) and ColorAdded (Yes/No) flags, as these are often noted in enthusiast databases.
ReleaseNotes or Description – text describing this release (e.g. “Fifth edition of the Supernova series, peatiest Ardbeg expression to date”).

Relationships & Constraints: The combination of key attributes (Brand + Name/Series + Age/Vintage + ReleaseYear + CaskType, etc.) should ideally be unique for each whiskey entry to prevent duplicates. A database constraint or index can be used on a suitable combination, for example: no two entries with the same Brand, Series, VintageYear, and ReleaseYear should exist. This ensures that each specific edition is only entered once. In practice, before adding a new whiskey, the system can check these fields against existing records to detect a duplicate. By structuring the data into fields (instead of one big name string), it’s easier to programmatically catch duplicates – e.g., two users trying to add "Glenfiddich 19 Age of Discovery Red Wine" with the same attributes would hit a uniqueness check on (BrandID, SeriesID, ReleaseYear) and be prevented from creating a redundant entry.

WhiskeySeries (Edition Group)

This table defines groups or “series” of related whiskey releases. It allows the database to link multiple Whiskey entries that are essentially different editions of the same product line. For example, Ardbeg’s Supernova series has multiple yearly releases under the same name​. Instead of treating them as unrelated entries, they can be grouped under one series for clarity.

SeriesID (PK) – Unique identifier for the series.
BrandID (FK to Brand) – The brand that the series belongs to (e.g. Ardbeg for all Supernova releases).
SeriesName – Name of the series or line (e.g. “Supernova”, “Limited Edition Small Batch”, “Age of Discovery”). This is typically a name that would appear on the label across all editions in the series.
TypeID – Optional: If all whiskeys in the series share the same type (often true, e.g. Supernova series are all Single Malt Scotch), this can be recorded for quick reference.
Description – Optional: A description of the series, if needed (e.g. “Annual ultra-peated release by Ardbeg for Committee members”).

Each Whiskey entry that is part of a series links to SeriesID. This enables features like showing all releases in the “Age of Discovery” series together. If a whiskey is a one-off or a core product not part of a recurring series, this field can be NULL. (In some implementations, even one-off products might have a series entry identical to its name for consistency, but it’s not required.) Grouping via this table prevents confusion between, say, “Highland Park Viking Pride 18 (2018 release)” vs “Highland Park Viking Pride 18 (2019 release)” – they would share a Series to denote they are editions of the same line, just different years. This structure cleanly supports multiple editions without duplicating the base name or brand info for each entry.

WhiskeyType

A lookup table for types or categories of whiskey. This ensures consistent classification of whiskey style.

TypeID (PK) – Unique ID for the type.
Name – Category name (examples: Single Malt Scotch, Blended Scotch, Bourbon, Rye, Irish Single Pot Still, Japanese Blended, etc.).

Using a separate table for whiskey type enforces a controlled vocabulary (e.g., preventing one entry from saying “Single Malt” and another “Single malt whisky” – all will use a standard term). This field helps users filter or search by style (e.g., show only Bourbons or only Single Malts).

CaskType

A lookup table for common cask maturation types.

CaskTypeID (PK) – Unique ID for cask type.
Name – Name of cask or finish type (examples: Ex-Bourbon, Oloroso Sherry, Pedro Ximénez Sherry, Port, Rum Barrel, New Charred Oak, Wine (Red), Madeira, etc.).

This table holds standardized names of cask types to avoid inconsistent terminology. For instance, one user might enter “Bourbon Barrel” and another “Ex-bourbon” for the same concept – by selecting from this list, all entries use the same term. In the Whiskey table, the CaskType field links here, so a Sherry-finished whisky will always be labeled with the exact cask type chosen (e.g., Oloroso) and not vague or misspelled variants. Normalizing cask types is important for user clarity since cask influence is a key differentiator between releases.

UserBottle (User Collection / Inventory) – Optional

This join table links users to the whiskeys in their collection. It allows each user to have a list of bottles without duplicating the master Whiskey entries.

UserID (FK to User)
WhiskeyID (FK to Whiskey)
OwnedQuantity – Optional: number of bottles the user has of this whiskey (if the app tracks quantity).
Notes/Rating – Optional: user’s personal notes or rating for this bottle.

Constraints: A composite unique key on (UserID, WhiskeyID) ensures a user cannot add the exact same whiskey entry twice to their list (preventing accidental duplicates of the same bottle in one collection). If a user owns multiples of the same whiskey, the OwnedQuantity can be incremented rather than creating a duplicate entry. This design keeps the global whiskey data unique while associating entries with many users.

Normalization & Naming Rules

To ensure bottle names and details are labeled consistently (and not confusing to users), the database enforces several normalization rules and naming conventions:

Separate Fields for Key Attributes: Information is stored in discrete fields rather than in one name string. For example, the age, ABV, and cask are kept in their own columns instead of being part of the name. This enforcement of 3rd normal form reduces data repetition and improves integrity​. It means that a whiskey’s name can be generated from its parts, and each part is consistently recorded. (E.g., you don’t have one entry named “Lagavulin 16 Year 43%” and another “Lagavulin 16 YO” as free text; instead Age=16, ABV=43 in their fields, and the display name is formatted from those.) This makes the labeling uniform.

Consistent Brand/Distillery Naming: Always associate each whiskey with a Brand and (where applicable) a Distillery from the predefined tables. This guarantees consistent spelling and format of producer names. Users will always see the brand/distillery name exactly as defined (no random abbreviations). For example: use “Bruichladdich” for all releases from that distillery’s brand, and avoid one entry saying “Bruichladdich” and another “Bruich Laddich”. The brand name is always prominently displayed alongside the whiskey, as it’s the first thing people look for on a label​.

Whiskey Name Formatting: Construct display names in a clear, standardized way, combining the fields. A recommended format might be: “Brand Name – Expression/Series Name – Age (if stated) – [Vintage Year or Release Year if needed] – Cask/Finish (if needed)”. Examples:
“Glenlivet – 12 Year Old” (age stated, part of core range so no extra series or year needed).
“Ardbeg – Supernova 2019 – Committee Release” (brand, series + year, plus edition tag).
“Glenfiddich – Age of Discovery (Red Wine Finish) – 19 Year – 2012 Release” (brand, series name with edition in parentheses, age, and release year).

In the UI, some of these components might be shown in separate columns (Brand, Age, Year, etc.), but when shown as one label, the order and punctuation should be consistent across the app.

Include Age Statements Consistently: If a whiskey has an age statement, always include it as “X Year” (or “X Years” or “X YO” – pick one format and stick to it). If it’s NAS (no age statement), do not fabricate an age or use misleading terms. You may omit the age in the display or use a standard marker like “NAS”. Avoid substituting marketing terms like “Rare” or “Special” in place of an age – those do not count as age statements​. (For example, a bottle labeled “Rare Old” with no number should have Age field = null and be displayed without an age, rather than as “Rare” age.) All age values are numeric and represent the youngest spirit in the bottle in years. This consistency helps users quickly identify the maturity of the whiskey or know that none is given.

Vintage and Year Notation: If VintageYear (distillation year) is used, and the whiskey is also aged or released in a certain year, use a clear convention to convey this. For instance, you might include it in the name as “Vintage 1998, Bottled 2018” or “1998/2018”. The schema stores these separately, but when presenting to the user, ensure it’s not confusing. A user should be able to distinguish a vintage year from an age. For example, a whiskey might be displayed as “Balblair 1991 (bottled 2018) – 27 Year Old”. If a release year is important for a series (like annual editions), include the year in the name as shown in the series examples above. Rule of thumb: any two entries with the same base name that differ by year should always show the year in the label to differentiate them.

Cask/Finish in Naming: Many whiskies have multiple versions differentiated only by cask finish or type (e.g. a Sherry Oak vs Bourbon Oak release, or a Red Wine finish vs Rum finish in the same series). To avoid confusion, include the cask type or finish in the product name if it’s a significant identifier. The schema has a CaskType field; when generating the display name or description, append or subtitle with that information. For example: “The Balvenie 15 Year Single Barrel – Sherry Cask” vs “… – Bourbon Cask”. This way a user can tell at a glance why two entries with otherwise the same name are different. Only do this if it’s part of how the product is distinguished; if every release in a series uses the same cask type, you might not need to repeat it in the name every time (since the series name or description covers it). Use parentheses or hyphens consistently to set off the cask type in the naming.

Independent Bottling Clarity: When the Brand is not the distillery (i.e., independent bottlers or sourced brands), ensure the distillery name is visible to the user so they know what they’re actually drinking. The database stores both, but the front-end should label it clearly, e.g., “Laphroaig 12 Year (Signatory)” or have separate fields like “Distillery: Laphroaig, Bottler: Signatory Vintage”. This prevents confusion where a user might see just the bottler’s name and not realize the distillery, or vice versa. For example, an entry could be shown as: “Signatory Laphroaig 12 Year”, meaning Signatory bottled it, Laphroaig distilled it. Another approach is to list it as “Laphroaig 12 Year (Signatory Vintage)”. The rule is to never omit the distillery for independent bottlings in the user-facing label – that’s key info. Conversely, for official distillery bottlings, you don’t need to repeat the distillery name twice (since Brand = Distillery in that case). So a Macallan official release can just say “Macallan 18 Year” (Brand covers it), not “Macallan (Macallan) 18 Year”.

Controlled Vocabulary & Codes: Use the enumerated tables for Type and Cask instead of free text. This means when adding a new whiskey, the type must be one of the predefined categories (or a new one added to the Type table deliberately). Similarly, cask types should be chosen from the CaskType list. This avoids inconsistent terminology (like one entry saying “Sherry Finish” and another saying “Finished in Oloroso” – if Oloroso Sherry is a defined term, everyone should use that). Maintaining a controlled vocabulary ensures users see consistent terms and can search/filter accurately. It also helps deduplicate entries (two entries won’t appear separate just because one wrote “Port pipe” and another “Port Pipe Finish” – the schema would have a single term for it).

Avoid Redundancy in Naming: Because the schema splits attributes, the UI should avoid redundantly repeating info. For example, don’t include the brand name again in the Name field if the context already shows the brand. If a listing shows a table with Brand and Name in separate columns, the Name column can just show “18 Year Old” instead of “Macallan 18 Year Old”, since the Brand column says Macallan. Define clear guidelines for the front-end on this. However, if you are showing a single string (like in a dropdown of whiskeys), it should probably concatenate Brand + Name + Age for uniqueness. Essentially, have a single source of truth for each piece of data and display it in the proper place. If the age or type is shown in dedicated UI fields, you don’t need it in the name string. If not, include it in the name string. Consistency here means users won’t see one label listed as “Lagavulin 16 Year” and another as “16 YO Lagavulin” – all follow the same pattern.

By adhering to these normalization and naming rules, the database presents whiskey entries in a uniform, predictable manner. Users can easily scan and understand key details (brand, type, age, etc.) without confusion from inconsistent labels. The use of separate tables and fields for each attribute means updates or corrections propagate consistently – e.g., if a distillery name is corrected, it updates for all bottles from that distillery, and if we decide to change how we display ages (say from “YO” to “Year Old”), we can do so systematically. Overall, normalization here serves both data integrity and user-friendliness.

Deduplication Strategy

A robust deduplication strategy is critical to ensure each whiskey edition appears only once in the database, while still allowing users and yearly series to be handled properly. Duplicate records (two entries referring to the same actual bottle) can confuse users and skew data​, so the system is designed to prevent and eliminate them:

Single Source of Truth for Each Bottle: The schema’s normalization inherently helps deduplication by not relying on a single text field for identification. We treat the combination of attributes as the identity. When a new entry is being added, the application should check for an existing entry with the same key attributes. For example, if someone tries to add “Lagavulin 16 Year 43%” and one already exists, the system will detect that Brand = Lagavulin, Age = 16, ABV = 43% matches an existing record and will not create a second. This could be enforced with a unique composite index on important columns in the Whiskey table (e.g. BrandID + Name + Age + VintageYear + ReleaseYear + CaskType should be unique, as that encompasses all differentiators). In practice, an exact match on all these fields means it’s the same whiskey. This database rule, along with application-level checks, eliminates duplicate data entries which keeps the data clean and avoids confusing repetition.

Fuzzy Matching for Similar Entries: Not all duplicates will be exact field matches (users might attempt to input a duplicate with slightly different spelling or incomplete data). Implement a deduplication check at entry time: when a user adds a new bottle, search by brand and name keywords to see if a similar entry exists. For example, if a user tries to add “Johnnie Walker Blue Label 2021”, the system can alert that “Johnnie Walker Blue Label (no year specified)” or a 2021 edition already exists, to prevent a duplicate. This might involve searching by Brand plus known series/name and checking year. Admins or moderators can review potential duplicates flagged this way. By catching duplicates before they are saved, we reduce cleanup later.

User Collection Uniqueness: Each user’s personal collection should not have duplicates of the same whiskey entry. This is handled by the UserBottle table’s unique constraint on (UserID, WhiskeyID). If a user tries to add a whiskey to their list that they already have, the system can instead prompt “You already have this bottle in your collection. Update the quantity if you have another one.” This ensures that within a user’s list, every entry is unique and represents a distinct whiskey. It also means any user-specific data (like rating or notes) about that whiskey is attached to one record per user. This strategy avoids the scenario of one user accidentally listing the same bottle twice (which could lead to double counting in collection stats, etc.).

Grouping of Editions (No Duplication for Series): When a whiskey has multiple editions or yearly releases, we use the WhiskeySeries table to group them rather than creating one “mega entry” or duplicating base data for each year. For example, Ardbeg Supernova releases in 2009, 2015, 2019 etc. are all linked under the Supernova series​. The base information (that it’s an Ardbeg, ultra-peated series) can be described at the series level, and each year’s edition has its specific data (year, ABV, etc.). This means we aren’t duplicating the text “Ardbeg Supernova” and its general description for every release – the series table holds it once, and each release references it. To the user, the grouping is evident (they might see “Supernova – 5 editions (2009, 2010, 2014, 2015, 2019)” on the series page). From a data standpoint, grouping eliminates duplicate brand/expression entries and makes maintenance easier (if the series name changes or we want to add a note, we change it in one place). It also helps dedup: if someone tries to add a new Supernova entry for 2019 while one already exists, the combination of series and year would conflict and prevent it. In summary, treat recurring releases as separate records tied to one group, not as one record with multiple values, and not as unrelated records with the same name (which would be duplicates). This approach preserves uniqueness while still connecting related items.

Managed Duplication for Re-releases: In some cases, the same whiskey might be re-released or very similarly released twice (e.g., a batch in one year and a second batch in the same year with identical specs). The schema can handle this by including batch or edition identifiers as part of the uniqueness. Our deduplication strategy would consider those two as distinct if, say, the batch number differs, so they won’t be merged erroneously. However, if the intent is that they are effectively the “same” product released again, one could link them via Series or another attribute. Deciding this comes down to how the product is meant to be represented to users. The rule here is: only truly identical items should be deduplicated. If there is any genuine difference (different batch, different bottling date, special vs general release), we treat it as a distinct entry (not a duplicate), but we use grouping or clear naming to relate it to its siblings. This ensures we’re not lumping different whiskies together nor splitting one whiskey into duplicates.

Periodic Audit and Merge: As a governance measure, maintain a routine (or admin tool) to find and merge duplicates that might slip through. Because the schema is highly normalized, blatant duplicates are unlikely (the unique index and checks will stop most). But if, for instance, two nearly identical entries were created (maybe one without a vintage year filled in and one with it, for the same whiskey), an admin can notice this and merge them by updating the record or marking one as deprecated. Regular audits can query for entries with the same Brand and Name that might indicate a duplicate. Deduplication isn’t just a one-time task; it’s an ongoing process to “consolidate information into a single data source”, preserving consistency​.

User Interface for Deduplication: Assist users in finding their bottle in the database before adding a new one. For example, when a user wants to add a bottle, provide an auto-complete search on existing entries. If the bottle is already in the master database, the user can select it (and perhaps just add it to their collection) rather than creating a new entry. This leverages the global unique list and avoids duplicate creation. Only if the search truly finds nothing should a new entry be created, and even then the user is guided to fill in structured fields (brand, etc.) to maintain consistency. This user flow is a practical part of the deduplication strategy.

In summary, the deduplication strategy is about one whiskey, one record in the master database, referenced by many users if needed. Through a combination of database constraints, intelligent grouping, and front-end design, we ensure that each unique whiskey (defined by its distinguishing attributes) appears only once. This eliminates confusion from duplicate listings​ and lets features like user reviews or ratings aggregate on that single entry. Meanwhile, the grouping of yearly or batch editions provides the flexibility to have multiple unique entries under a common umbrella without confusing them as duplicates. The end result is a clean, user-friendly whiskey database where every bottle is uniquely and consistently identified, and both data managers and end users can navigate it without ambiguity.

The issue names no files, tests, or entry points. Start by locating the schema definitions and existing normalization or deduplication logic, then compare them with the proposed Brand, Whiskey, series, and release attributes; done means the agreed schema and uniqueness approach are implemented consistently.