Sfrgttk

Manager has noticed coworker's excessive breaks. Should I warn him?

Coworker is trying to get me to sign his petition to run for office. How to decline politely?

Do error bars on probabilities have any meaning?

How bad is a Computer Science course that doesn't teach Design Patterns?

Does an increasing sequence of reals converge if the difference of consecutive terms approaches zero?

Which was the first story to feature space elevators?

Hollowed circle with crossed line and arrow

What caused Doctor Strange to repent of his selfishness and become Earth's protector?

Does a star need to be inside a galaxy?

How does the income of your target audience matter for logo design?

Would plants on a planet orbiting a star like ours, have any different color?

Why did some CPUs use two Read/Write lines, and others just one?

Is Screenshot Time-tracking Common?

How can I differentiate duration vs starting time

Short story in which aliens follow chain of increasingly ferocious/belligerent animals culminating with human child

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Get category id

What happens if you declare more than $10,000 at the US border?

Run a command that requires sudo after a time has passed

Why would you use 2 alternate layout buttons instead of 1, when only one can be selected at once

I hate taking lectures, can I still survive in academia?

Is it ethical to apply for a job on someone's behalf?

Symbolism of number of crows?

How can changes in personality/values of a person who turned into a vampire be explained?

1 to N relationship on a product shop database design

Relational Design - Multiple tables into one foreign key column?How could I handle heavy load and consistency for product inventory in online retailers?Inventory Database Design IssueDesign Pattern - One of Many Parent TablesHow to design data about a column can belong to the whole table?Constraint spanning several tablesDatabase schema design for individual configurationsModeling discounts - Product specific and Customer specific discountsFact to Fact relationship in DW DesignTwo type users configuration

0

imagine this scenario:

I want to run a database that keeps track of all the purchases that every customer makes.
So i'll have a purchases table along a customers table and a prodcuts table.
The thing is that one purchase is made of many objects.
To keep track of this my idea is to make another table objectslist that contains: a primary key as as generic id, a foreign key as the purchaseid that references the purchase table, and the product id as a foreign key from the products table.

The thing becomes tricky for 2 reasons:
inserting a purchase can be troublesome because i've got to update 3 tables at once.
Querying for a specific purchase and all the objects on the cart involves multiple joins and i haven't figured out how to do this yet.

If there's any other simple idea let me know.
Thanks

database-design

share|improve this question

edited May 3 '15 at 11:33

a_horse_with_no_name

40k776112

asked May 3 '15 at 11:30

lemonheads1212lemonheads1212

1

bumped to the homepage by Community♦ 15 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can use a trigger on the relevant tables to perform the updates. If you want an example of a store database you can check out AdvertureWorks for SQLServer or You can download OpenCart (it's a PHP based Open-Source eCommerce store, contains everything you said.)
  
  – ZeroBased_IX
  May 3 '15 at 12:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Seems like a reasonable design. A purchase may contain many products and a product may appear in many purchases so it's clearly a n-to-n relationship. What's troubling you with joininig multiple tables? As for updating 3 tables at once, that's what transactions are for.
  
  – zgguy
  May 3 '15 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you have the standard Order/OrderItem table layout, you already track all the purchases of every customer. What more do you need?
  
  – TommCatt
  May 4 '15 at 23:34
  

  
  
  
  

add a comment | 

0

imagine this scenario:

I want to run a database that keeps track of all the purchases that every customer makes.
So i'll have a purchases table along a customers table and a prodcuts table.
The thing is that one purchase is made of many objects.
To keep track of this my idea is to make another table objectslist that contains: a primary key as as generic id, a foreign key as the purchaseid that references the purchase table, and the product id as a foreign key from the products table.

The thing becomes tricky for 2 reasons:
inserting a purchase can be troublesome because i've got to update 3 tables at once.
Querying for a specific purchase and all the objects on the cart involves multiple joins and i haven't figured out how to do this yet.

If there's any other simple idea let me know.
Thanks

database-design

share|improve this question

edited May 3 '15 at 11:33

a_horse_with_no_name

40k776112

asked May 3 '15 at 11:30

lemonheads1212lemonheads1212

1

bumped to the homepage by Community♦ 15 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You can use a trigger on the relevant tables to perform the updates. If you want an example of a store database you can check out AdvertureWorks for SQLServer or You can download OpenCart (it's a PHP based Open-Source eCommerce store, contains everything you said.)
  
  – ZeroBased_IX
  May 3 '15 at 12:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Seems like a reasonable design. A purchase may contain many products and a product may appear in many purchases so it's clearly a n-to-n relationship. What's troubling you with joininig multiple tables? As for updating 3 tables at once, that's what transactions are for.
  
  – zgguy
  May 3 '15 at 12:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you have the standard Order/OrderItem table layout, you already track all the purchases of every customer. What more do you need?
  
  – TommCatt
  May 4 '15 at 23:34
  

  
  
  
  

add a comment | 

imagine this scenario:

I want to run a database that keeps track of all the purchases that every customer makes.
So i'll have a purchases table along a customers table and a prodcuts table.
The thing is that one purchase is made of many objects.
To keep track of this my idea is to make another table objectslist that contains: a primary key as as generic id, a foreign key as the purchaseid that references the purchase table, and the product id as a foreign key from the products table.

The thing becomes tricky for 2 reasons:
inserting a purchase can be troublesome because i've got to update 3 tables at once.
Querying for a specific purchase and all the objects on the cart involves multiple joins and i haven't figured out how to do this yet.

If there's any other simple idea let me know.
Thanks

database-design

share|improve this question

edited May 3 '15 at 11:33

a_horse_with_no_name

40k776112

asked May 3 '15 at 11:30

lemonheads1212lemonheads1212

1

share|improve this question

edited May 3 '15 at 11:33

a_horse_with_no_name

40k776112

asked May 3 '15 at 11:30

lemonheads1212lemonheads1212

1

share|improve this question

edited May 3 '15 at 11:33

a_horse_with_no_name

40k776112

asked May 3 '15 at 11:30

lemonheads1212lemonheads1212

1

edited May 3 '15 at 11:33

a_horse_with_no_name

40k776112

edited May 3 '15 at 11:33

a_horse_with_no_name

40k776112

asked May 3 '15 at 11:30

lemonheads1212lemonheads1212

1

asked May 3 '15 at 11:30

lemonheads1212lemonheads1212

1

1 Answer
1

active

oldest

votes

0

When you make a purchase, you're really only inserting into two tables: Purchase and ObjectsList.

Querying for a Purchase and the Products in a Purchase is simple:

SELECT

    *

FROM

    Purchase JOIN ObjectsList

        ON ( Purchase.idPurchase = ObjectsList.idPurchase )

    JOIN Product

        ON ( ObjectsList.idProduct = Product.idProduct )

WHERE idPurchase = ?;

I think your design is good, there's nothing wrong with needing to insert into or query multiple tables.

share|improve this answer

answered May 3 '15 at 17:24

Joe BoryskoJoe Borysko

36127

add a comment | 

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
});


}
});

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f100461%2f1-to-n-relationship-on-a-product-shop-database-design%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

When you make a purchase, you're really only inserting into two tables: Purchase and ObjectsList.

Querying for a Purchase and the Products in a Purchase is simple:

SELECT

    *

FROM

    Purchase JOIN ObjectsList

        ON ( Purchase.idPurchase = ObjectsList.idPurchase )

    JOIN Product

        ON ( ObjectsList.idProduct = Product.idProduct )

WHERE idPurchase = ?;

I think your design is good, there's nothing wrong with needing to insert into or query multiple tables.

share|improve this answer

answered May 3 '15 at 17:24

Joe BoryskoJoe Borysko

36127

add a comment | 

0

When you make a purchase, you're really only inserting into two tables: Purchase and ObjectsList.

Querying for a Purchase and the Products in a Purchase is simple:

SELECT

    *

FROM

    Purchase JOIN ObjectsList

        ON ( Purchase.idPurchase = ObjectsList.idPurchase )

    JOIN Product

        ON ( ObjectsList.idProduct = Product.idProduct )

WHERE idPurchase = ?;

I think your design is good, there's nothing wrong with needing to insert into or query multiple tables.

share|improve this answer

answered May 3 '15 at 17:24

Joe BoryskoJoe Borysko

36127

add a comment | 

When you make a purchase, you're really only inserting into two tables: Purchase and ObjectsList.

Querying for a Purchase and the Products in a Purchase is simple:

SELECT

    *

FROM

    Purchase JOIN ObjectsList

        ON ( Purchase.idPurchase = ObjectsList.idPurchase )

    JOIN Product

        ON ( ObjectsList.idProduct = Product.idProduct )

WHERE idPurchase = ?;

I think your design is good, there's nothing wrong with needing to insert into or query multiple tables.

share|improve this answer

answered May 3 '15 at 17:24

Joe BoryskoJoe Borysko

36127

SELECT

    *

FROM

    Purchase JOIN ObjectsList

        ON ( Purchase.idPurchase = ObjectsList.idPurchase )

    JOIN Product

        ON ( ObjectsList.idProduct = Product.idProduct )

WHERE idPurchase = ?;

share|improve this answer

answered May 3 '15 at 17:24

Joe BoryskoJoe Borysko

36127

answered May 3 '15 at 17:24

Joe BoryskoJoe Borysko

36127

answered May 3 '15 at 17:24

Joe BoryskoJoe Borysko

36127