Database Design for Product Promotions
Assume I have a products table with the following columns:
id(PK)
category_id(FK)
color_id(FK)
material_id(FK)
seller_id(FK)
size_id(FK)nameprice
Further assume that there could be 0 or more promotions that apply to a product. A promotion will have a start and end date / time, a discount amount (e.g. $100 off) or percentage (e.g. 10% off), etc.
The promotion will apply only to products that match a certain criteria, such as:
- all products that are "red" color and "large" size
- all products that are under the "shirts" category
- all products sold by
seller_id= 1 andprice> $50
How would I represent this in a database? I feel that I need to associate one or more "query string" to the promotions table (e.g. ?color_ids=1&sizes=3) and test each product against all query strings to see if that product is part of that promotion. I expect this to be very inefficient, though.
Another option is to have a table products_promotions that gets set whenever a product or promotion is inserted / updated in the system, then it's just a matter of checking that table to see if the product has any promotions associated with it.
Thoughts?
mysql database-design
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
add a comment |
Assume I have a products table with the following columns:
id(PK)
category_id(FK)
color_id(FK)
material_id(FK)
seller_id(FK)
size_id(FK)nameprice
Further assume that there could be 0 or more promotions that apply to a product. A promotion will have a start and end date / time, a discount amount (e.g. $100 off) or percentage (e.g. 10% off), etc.
The promotion will apply only to products that match a certain criteria, such as:
- all products that are "red" color and "large" size
- all products that are under the "shirts" category
- all products sold by
seller_id= 1 andprice> $50
How would I represent this in a database? I feel that I need to associate one or more "query string" to the promotions table (e.g. ?color_ids=1&sizes=3) and test each product against all query strings to see if that product is part of that promotion. I expect this to be very inefficient, though.
Another option is to have a table products_promotions that gets set whenever a product or promotion is inserted / updated in the system, then it's just a matter of checking that table to see if the product has any promotions associated with it.
Thoughts?
mysql database-design
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
add a comment |
Assume I have a products table with the following columns:
id(PK)
category_id(FK)
color_id(FK)
material_id(FK)
seller_id(FK)
size_id(FK)nameprice
Further assume that there could be 0 or more promotions that apply to a product. A promotion will have a start and end date / time, a discount amount (e.g. $100 off) or percentage (e.g. 10% off), etc.
The promotion will apply only to products that match a certain criteria, such as:
- all products that are "red" color and "large" size
- all products that are under the "shirts" category
- all products sold by
seller_id= 1 andprice> $50
How would I represent this in a database? I feel that I need to associate one or more "query string" to the promotions table (e.g. ?color_ids=1&sizes=3) and test each product against all query strings to see if that product is part of that promotion. I expect this to be very inefficient, though.
Another option is to have a table products_promotions that gets set whenever a product or promotion is inserted / updated in the system, then it's just a matter of checking that table to see if the product has any promotions associated with it.
Thoughts?
mysql database-design
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
Assume I have a products table with the following columns:
id(PK)
category_id(FK)
color_id(FK)
material_id(FK)
seller_id(FK)
size_id(FK)nameprice
Further assume that there could be 0 or more promotions that apply to a product. A promotion will have a start and end date / time, a discount amount (e.g. $100 off) or percentage (e.g. 10% off), etc.
The promotion will apply only to products that match a certain criteria, such as:
- all products that are "red" color and "large" size
- all products that are under the "shirts" category
- all products sold by
seller_id= 1 andprice> $50
How would I represent this in a database? I feel that I need to associate one or more "query string" to the promotions table (e.g. ?color_ids=1&sizes=3) and test each product against all query strings to see if that product is part of that promotion. I expect this to be very inefficient, though.
Another option is to have a table products_promotions that gets set whenever a product or promotion is inserted / updated in the system, then it's just a matter of checking that table to see if the product has any promotions associated with it.
Thoughts?
mysql database-design
mysql database-design
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
asked 4 mins ago
DatabaseNewbieDatabaseNewbie
15516
15516
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232083%2fdatabase-design-for-product-promotions%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232083%2fdatabase-design-for-product-promotions%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
