0

I am trying to create a table in my database using Visual Studio. I've got a table for my Products (like in online shop) and then I have a table for Orders, which should store all products that user has ordered. The problem is that I am not sure which datatype I should use when designing the database to store an array of products in my Orders table. This is what the Orders table should look like

Improve this question
3
  • 1
    This is not what you want to do. You want to have two tables here. Orders and OrderLine. Commented Dec 15, 2016 at 19:58
  • Third table ProductionOrders will be your solution Commented Dec 15, 2016 at 20:03
  • Thank you. I considered using this method before but I took this example from a book and it never mentions creation of a third table. I will try doing it the way you suggested. Commented Dec 15, 2016 at 20:08

2 Answers 2

Reset to default
1

You should create Products and Orders table with relationship between them.

Your Orders table should have Id column as well (which is PrimaryKey) Then you should create Products table, that keeps all the information about products and additionaly OrderId which should be used as Foreign Key to Orders table.

Please look at that link: https://msdn.microsoft.com/en-us/library/ms189049.aspx

It's also worth of checking: One To One, One To Many, Many To Many relations in SQLServer to have better understanding and design your data store properly.

In your case you need ProductsOrders table, Many To Many relationship.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you. I considered using this method before. But this is an example from a book I tried to make and it never mentions creation of a third table.
to store array in one SQL column, you need to use something like ; separated string etc. It's not really approach you want to go. What's the book you are getting through?
I think theu are more focused on JavaScript side of code, and basic backend calls, that might be a reason why they just mentioned what are the data as opposed to how to do db layer step by step. Image you attached looks like type definition not table as well
Thank you. The image says it is "The Properties Required for the Orders"
Yeah, it's definitely data type for JavaScript / TypeScript then.
0

In Relational database, you can create a relationship between 2 tables.

The relationship can be

  • 1 to 1 (1 Product - 1 Order)
  • 1 to Many (1 Product - 'n' Order)
  • Many to Many (n product - 'n' Order)

Based on your scenario, You can choose any of the relationship listed above. While querying from the database, you can easily operate over each order/Product.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.