How a SQL Server Works / Best Method of loading data

[Papa_Don]

Hi group,

After reviewing notes, I think I understand how to write the code to save data in the SQL Server. With that said, I want to understand the best way to arrange the data and upload it.

My project is creating an order. I'll have the usual part number, quantity ordered, price, extended price, etc. to save from each line. My question is, when I'm ready to save the line through code, do I think of what I'm saving as a line? That is, with each line I need to fill the "cells" of each column with ALL data, even that of the "static" information (order number, customer name, etc.)? In other words, should the "line" of the database look like this"

Order Number | Customer Name | Part Number | Quantity | U/M |
    321456   | Don Wilson    | AO2544SP    |  52      | SF  |
    321456   | Don Wilson    | AO2566SP    | 150      | SF  |

The "static" info I'm referring to are things like the Order Number and Customer Name. However things like Part Number, Quantity and Unit of Measure are the "variable" that will change from line to line. Keep in mind I'm asking this question as I'm sure at some point I'll want to create reports to show this info.

Hopefully I'm making sense here.

In advance, thanks for the help.

Don

[Reverend Jim]

Ideally, the customer name should not appear more than once. What you should have is an Orders table that contains (possibly) the fields

OrderNo
CustomerName
OrderDate
etc

where you have only one record per order, and an OrderItems table with one record per order item such as

OrderNo
PartNo
Quantity
UnitCost
etc

Compare it to a paper order which has the information from the Orders table at the top of the page followed by a table containing the items.

[Papa_Don]

Rev. Jim,

Forgive me for not following. I previously worked with a UNIX based system and wrote reports to pull detail from the databases. The system had the the ability to drop the output into an EXCEL spreadsheet. In that, I would pull the CustomerNo, CustomerName, OrderNo, LineNo, PartNo, Description, QuantitySold, Price, Cost, etc. When it displayed the output via EXCEL, every cell had data in it. In a multi-line order, CustomerNo, CustomerName, OrderNo were each filled and were identical. The difference in these multi-lines were the LineNo, PartNo, Description, QuantitySold, etc. Because of the way "DataGridView" in SQL displayed (it looks like a spreadsheet), my assumption was that the data was saved the same way in SQL.

With this said though, I have designed my database (with Open and Closed Orders) as a database of just "Header" information - that is the OrderNo, CustomerNo, CustomerName, etc. There is a separate database for the "Line Information" with LineNo, PartNo, Description, QuanityOrdered (or Shipped), Price, etc. The link between the two would be the OrderNo.

It seems to me what you are saying is that I can save the data once for "header" info and save the Line information with each order line. Am I getting that correct?

I really wish the instructor had spent more time with databases, setting them up, writting to them, etc. It sure would have made this project much easier.

Jim, thanks again for your help.

Don