-2

I believe everyone encountered this example numerous times, where there is a primary/foreign key relation. Now, I am interested when to add just a foreign key ID instead of all/some relevant foreign key information.

For example

Product

ID
Value
Name
Description

Order

ID
ProductID
Value
Name
Description

vs

Order

ID
Value
Name

vs

Order

ID
ProductID
Value
4
  • 2
  • 1
    Also, im not sure this has anything to with C# specifically
    – richzilla
    Feb 3, 2017 at 15:00
  • It can be a ddd issue or a database issue. So c# is a valid tag imo
    – John
    Feb 3, 2017 at 15:46
  • What you are showing is not a parent/child relationship, since both Product and Orders are first-class entities. That the Order refers to a Product is merely a reference, not a child, in particular because if you delete the Order the Product is not deleted. On the other hand, an order with multiple line items is a parent/child relationship, as if the order is deleted, so should the line items be. So, is your question about parent/child relationships or about caching content when making an (ordinary) reference?
    – Erik Eidt
    Feb 3, 2017 at 17:20

2 Answers 2

2

The short answer is 'never' (caveats apply).

If you have a parent child relationship, foreign keys give you a natural way of modelling this. Consider these situations:

  • You have several orders that use the same product, immediately you have duplicated data for each order.
  • What if you want a way to model situations where orders have multiple products.
  • If you want to modify the details of a product (its price, for example), you have to modify it everywhere its used.
  • What if you have other situations where you want to use product information (stock control, perhaps). You need to copy all of the data to these locations aswell
  • If you do need to use your product information in multiple locations, all of the above issues are multiplied

Technically, this area of database development is called database normalisation, and your specific case (using foreign keys to refer to related records) is called first normal form. Its probably worth reading up on those topics.

(caveats) The slightly longer answer is 'probably never', unless your application is extremely performance sensitive and the performance requirements outweigh the cost of dealing with points raised above. One such example is a data warehouse, where old data is guaranteed not to change and performance can benefit from some duplicated data without the risks mentioned above. For everything else, normalize your data.

7
  • 2
    You said what I was going to say minus the little bit I just added: have an upvote.
    – user22815
    Feb 3, 2017 at 15:21
  • I think you missed a point. What happens when product price changes but should not affect the order?
    – John
    Feb 3, 2017 at 15:40
  • 1
    In that case, you would need to store the price at that time. At that point though, the price would no longer be a property of the of the product, but a separate concept that represents 'product price at some point in time'. I still dont think it belongs on the Order.
    – richzilla
    Feb 3, 2017 at 15:46
  • Hm, i don't agree with you. Order actually represents a product price at a time of creating order, so it should be stored in order.
    – John
    Feb 3, 2017 at 16:05
  • Well... as much as I'm reluctant to get dragged into someone else's application design, that would work fine if you were absolutely sure that your orders would only ever have a single product
    – richzilla
    Feb 3, 2017 at 16:39
0

What you are trying to accomplish is simple but take into account that you have one well defined 'Product_table' with a unique ProductID as a PK with relations 1:n and the 'Order_table' is going to have a 'OrderID' as a PK with relations 1:n. The 'Sales_table' is going to have a 'row_ID'as a unique value instead the orderID will appear several times along with the several ProductID that you are going to attach to an order each constrained by FK ProductID and FK OrderID. This is what happens in real life that one order has many products in it and the only way to put them together is one table that stores the relation of one OrderID to many ProductID included in a sales order

Product_table

ProductID (PK)

Price

Name

Description

Order_table

OrderID (PK)

Value

Name

Description

Sales_table

row_ID

OrderID

ProductID

Use price for single price of each product and use value for total of order as a naming convention.You must enter the order data before you finalize the sales receipt so you update the sales table after

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.