i'm trying to create with sequelize (postgre) 'Ingredient' table, with columns normalizedName/userId where normalizedName is unique per userId.
The second table is 'IngredientQuantity', with columns ingredientId/userId/quantity.
I tried to set in 'Ingredient' normalizedName and userId as primaryKey, and to foreign key this composite PK from 'IngredientQuantity' table with ingredientId, but i saw that was impossible with sequelize, only normalizedName is used for reference in foreign key.
Whats is the best approach to do that ? I thought about id auto increment, but all id are shared among all users. For example user1 create his first ingredient with id = 1, when user2 create his first ingredient he will have id = 2. So. i don't know if it's good idea, if all users have lot of ingredients i should use bigint etc..and if they delete/add/delete/add id will grow up.
Ingredient table
module.exports = (sequelize, DataTypes) => {
var Ingredient = sequelize.define('ingredient', {
name: DataTypes.STRING,
normalizedName: { type: DataTypes.STRING, primaryKey: true },
userId: { type: DataTypes.INTEGER, primaryKey: true }
}, {
freezeTableName: true
});
Ingredient.associate = function (models) {
models.ingredient.hasMany(models.ingredientQuantity);
models.ingredient.belongsTo(models.user, {
onDelete: "CASCADE",
foreignKey: {
allowNull: false
}
});
};
return Ingredient;
};
IngredientQuantity table
module.exports = (sequelize, DataTypes) => {
var IngredientQuantity = sequelize.define('ingredientQuantity', {
quantity: DataTypes.FLOAT,
}, {
freezeTableName: true
});
IngredientQuantity.associate = function (models) {
models.ingredientQuantity.belongsTo(models.ingredient);
models.ingredientQuantity.belongsTo(models.user, {
onDelete: "CASCADE",
foreignKey: {
allowNull: false
}
});
};
return IngredientQuantity;
};
Whats is the best approach if i consider lot of data with lot of users ? Is there an other solution ? Thanks
It's totally normal to use SERIAL
as autoincremented integer surrogate PK. Also you can use UUID
as autogenerated PKs (in such case you should set default value as uuid_generate_v4()
) if you somehow afraid that integer value range will not be enough.
Because it's a service field there is no need it to be unique only for a certain user. Usually you shouldn't rely on a PK value.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments