SQL Database - Storing multiple entries of same type in one record

Draupnir

Hey guys,

Im designing a system which will store order data in microsoft sql server. My problem is that I dont know how many products are involved in each order so I cant set an amount of columns in the table ORDERS to store the products.

Can someone explain how I would do this?

Do I need a second table which would be related to the ORDERS table?

Can someone explain how I make a table so that it can grow depending on how many products are involved?

Cheers in advance

zod

yep an order item table .. with the order key in the second column.

like this

Order Table
OrderPrimarykey Description Date etc


OrderItems
OrderitemsKey OrderKey Product Quantity CostEach etc

bonkey

Draupnir wrote:

Do I need a second table which would be related to the ORDERS table?

More or less....yes.

This second table would contain one entry for each product in an order.

Think about it...the order table would contain some information about the order, and then you'd have an "orderItem" table, with one entry in it for each product-item in an order. Presumably, the orderItem would also contain a quantity field (i.e. if I make an order for 10 of item X, and 5 of item Y, I shouldn't need 15 entries in OrderItem...only two).

Can someone explain how I make a table so that it can grow depending on how many products are involved?

You can't. And even if you could, you wouldn't.

What you'll have is a product table (product Id, name, maybe cost-per-item and so on). You'll have an order table (order id, date_of_order,customer information - maybe just a customerId to link to the customer table, and so on), and then you'll have an orderedProduct table (productId, orderId, quantity)

To add an order, you add one record to the Order table, and one record for each product ordered to orderedProduct.

jc

Draupnir

cheers lads, thats spot on. thanks for that.

ya know how it is, when ya work on a problem or idea for too long ya cant see the wood for the trees!