Flavor attributes

[pscn]

What do we want to store for a flavor? Is flavor even the correct naming? What about concentrate or flavor concentrate?

1. Facts (the things you would find on the vendor site and are usually not open for discussion): Vendor Reference (DB ID or UUID or …), Name (i18n?), Description (i18n?), Specific Weight, Suggested Percentage, Image(?), Link (URL) to vendor site…
2. Opinions (the things a user enters): Tags, Ratings, Reviews (i18n!), Links (URL) to reviews, Images(?), Suggested Percentages (as single flavor and in a mix?), Aliases (i18n? how is it named on other sites?), Link (URL) to shops(?)…
3. Dynamically calculated (based on usage in recipes): Average Percentage (as single and in a mix?)…

I'm not really sure how to do this best, so this issue should just get the ball rolling :) I see 1. as the "flavor" table and 2. as extra tables linked to the "flavor" table. 3. is a query or a view. Should we try to write down simple DDL for this, trying to nail down the naming and column types?

[Korlimann]

One thing I'd also like to include is stuff like if they contain DAAP or if there's any other information about them, for example how TFA Pear apparently melts plastic tanks, or TFA Butterscotch causes cancer. Debatably though if we should make this open to users or not.

[pscn]

Flags (or one attribute with a comma separated list) for DAAP and other ingredients is a good idea.

Another thing I forgot is the version (or revision?) of a flavor. Like when the formula has changed (see Inawera Milk Chocolate). That could also include a from / to timestamp.

And maybe something like a Variant for VG, PG based concentrates or SC (super concentrate) or...

[Korlimann]

Yes, VG and PG base would be nice to have. I suppose it's not THAT important, but we literally have a person on the DIY Discord that is allergic to PG, so she is constantly in a search for Flavors with VG base. If we could provide such people with the option to search for VG only flavors, that would be great

[pscn]

Started a wiki page here: https://github.com/Korlimann/MixnJuice/wiki/DDL:-Flavor

Feel free to edit / update / delete as you see fit or discuss here.

[daviddyess]

I have a test data set I'm working on and it's close to what you have layed out. My thinking is to keep the actual flavor table as simple as possible, because that is where a lot of your queries will be. The flavors table should basically be facts provided by the manufacturer. Then do supplemental tables and/or documents that can be pulled in to provide extra useful community data.

The web site can also have it's own data to supplement the API, which takes some traffic off the API server.

[daviddyess]

The SQL for the table in the wiki is pretty close to what I have actually :)

[nickdurcholz]

If we create a Flavor_i18n table, do we want to remove name & description from the Flavor table?

I feel like the answer to this question should be "yes". Commit to it or don't; you don't want to be in the business of running one query for english users and a different one for all other languages.

[pscn]

Do you have any experience how to best approach i18n in the database? I removed name & description from the flavor table for now. I guess the next step is to provide a view that joins flavor & flavor_i18n and that will be the thing that everybody queries? So an index on language_code should be a given. I'll need to lookup a better name for this.

[daviddyess]

Curious as well, I've never dealt with locales from a database, its always been file based for me. Although, it may not be too difficult to just have a json field that defines the name for each language and returns all names or a specified language "name". That would prevent a join and you could search in the json field by name and language.

On Mon, May 6, 2019, 10:27 PM Peter S. notifications@github.com wrote:

Do you have any experience how to best approach i18n in the database? I removed name & description from the flavor table for now. I guess the next step is to provide a view that joins flavor & flavor_i18n and that will be the thing that everybody queries? So an index on language_code should be a given. I'll need to lookup a better name for this.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/Korlimann/MixnJuice/issues/20#issuecomment-489903155, or mute the thread https://github.com/notifications/unsubscribe-auth/AACDNYWBGJVVLSFJPSTUSW3PUEAKZANCNFSM4HK7725A .

[pscn]

Queuetue from ATF was so kind and shared his DDL with us in Discord:

CREATE TABLE `flavors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `free` tinyint(1) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `recipe_count` int(11) DEFAULT NULL,
  `average_millipercent` int(11) DEFAULT NULL,
  `slug` varchar(255) NOT NULL DEFAULT '',
  `confirmed` tinyint(1) DEFAULT NULL,
  `vendor_url` varchar(255) DEFAULT NULL,
  `base` varchar(255) DEFAULT NULL,
  `density` float DEFAULT '1',
  `flavor_views_count` int(11) DEFAULT '0',
  `month_flavor_views_count` int(11) DEFAULT '0',
  `week_flavor_views_count` int(11) DEFAULT '0',
  `hour_flavor_views_count` int(11) DEFAULT '0',
  `image` varchar(255) DEFAULT NULL,
  `author_id` int(11) DEFAULT NULL,
  `confirmer_id` int(11) DEFAULT NULL,
  `rejected` tinyint(1) DEFAULT '0',
  `short_description` text,
  `description` text,
  `description_raw` text,
  `imageUrl` varchar(255) DEFAULT '',
  `vendor_percentage` float DEFAULT '0',
  `vendor_description_raw` text,
  `vendor_description` text,
  `other_names` varchar(255) DEFAULT '',
  `discontinued` tinyint(1) DEFAULT '0',
  `caution` tinyint(1) DEFAULT '0',
  `caution_text` varchar(255) DEFAULT '',
  `short_description_raw` text,
  PRIMARY KEY (`id`),

Do you use ints or strings for IDs?

I use a Long INT and a correlating 'slug' - both are unique for the tables that use slugs, like vendors for instance.