Help refactoring a horribly structured Sequelize models

This is my first time working with Sequelize and I've found myself with a twisted and nested relational structure that I don't think is right. I'm hoping you guys could point me in the right direction to make it clearer.​My database has 4 entities User, Store, Product, Offer. User can be either a regular user or store owner. Stores can set up a list of their Products. Stores can also set up offers on certain products. Users can "watch" Products from Stores and put them in a list.What makes it trickier is I need stores to be able to upload their products via a .JSON file, and the ID for the product needs to be a passed in SKU not an autogenerated ID. Since multiple stores could have a product with the same SKU I created an index fields: ["sku", "storeId"], unique: true. However, since I need a UserProduct mapping table for when a User adds a Product to their "watching" list I also included an ID field to Product that is an autogenerated ID that can be used as the foreign key in UserProducts. All in all my Product model looks like this:const Product = sequelize.define( "product", { id: { type: Sequelize.INTEGER, autoIncrement: true, allowNull: false, primaryKey: true }, sku: { type: Sequelize.STRING, allowNull: false, }, image_url: { type: Sequelize.STRING, allowNull: false }, storeId //added by the Sequelize relationships/foreign key }, { indexes: [{ fields: ["sku", "storeId"], unique: true }] } ); My Offer table is practically the same but includes a product_sku field where the Store owner can specify which Product this offer applies to (note this product_sku point to the user supplied SKU field not the autogenerated ID field):const Offer = sequelize.define( "offer", { id: { type: Sequelize.INTEGER, autoIncrement: true, allowNull: false, primaryKey: true }, offer_id: { type: Sequelize.STRING, allowNull: false, }, product_sku: { type: Sequelize.STRING, allowNull: false, }, storeID //added by the Sequelize relationships/foreign key }, { indexes: [{ fields: ["offer_id", "storeId"], unique: true }] } ); But now as you can see it has 4 different "ids" of one sort or another, as well as no foreign key relation to the Product it belongs to.I tried making the Offer belong to the Product instead of the Store, to create that relationship. However, that would require the store owner to put in the Product ID to attach it to the correct product not just the Product SKU, if I tried to add the offer via the Product SKU you again run into the issue that multiple stores can have products with the same SKU. For example, here is how I'm uploading the offers (and similarly the Products) with a custom upsert or create function:function upsert(values, condition) { return Offer.findOne({ where: condition }).then(function(obj) { if (obj) { // update return obj.update(values); } else { // insert return Offer.create(values); } }); }and this where uploadList is the array or parsed offers from the .JSON:for (const offer of uploadList) { upsert({ ...offer }, { offer_id: offer.offer_id, productId: offer.productId }) }This works if the productId in the JSON is the autogenerated product ID but I don't want the store owners to have to remember that I want it to go by SKU so they can remember.So in all what I'm looking for is a better way to structure my tables so that Stores can own many Products and Products can have many Offers. And let Products can be referenced by a combination of SKU (not autogenerated) and store ID. Any help would be appreciated.

Submitted November 16, 2018 at 03:11PM by DasBeasto