Proper normalization for an Orders table--whether to split or no Each order has a unique SalesOrderNumber. An order is made up of various PartID numbers, each with an associated OrderQuantity number. 

I have the following table currently in my model:



I'm wondering if this table should be split into two tables as such:



This would leave the Order table with only one row for each SalesOrderNumber, but I'm not sure if this is necessary from a normalization point of view. It seems that either arrangement should meet 2NF. I'm still a bit shaky with 3NF and BCNF however. 

Here is what a sample from the table might look like now, with only relevant columns displayed for a made up SalesOrderNumber of 12345:

SalesOrderNumber PartID OrderQuantity 
12345            1a     1 
12345            2b     2 
12345            3c     43
The objective of normalization is to remove update anomalies. Specifically, changing a single fact should result in a change to one column of one row only. Consider how this would apply in your proposed designs if, say, ShippingDestination were altered, which is a very reasonable business requirement.

In the first design multiple rows may change as, potentially, there are multiple rows for a single SalesOrderNumber, each with its own PartID. With the second design only a single row is updated. So the second design is more normalized.

For a different point of view you can look at the dependencies - what values change in unison. In the first design if you go from one row to another with the same SalesOrderNumber the ShippingDestination will be the same as will DueDate. However OrderQuantity will differ between these two rows. So OrderQuantity depends on more than just SalesOrderNumber - you need to know PartID also. Two different dependencies so two different normalised tables. (There's more to Functional Dependencies / Dependency Decomposition, but that's the gist of it.)

2NF talks about "part key dependency" - whether all of the columns in the key are required to determine all non-key columns' values. Looking at the first design there is a composite key of (SalesOrderNumber, PartID). As we move from one row with a particular SalesOrderNumber to the next with a the same number some columns (ShippingDestination for example) will always be the same by definition. PartID is in the key but is not required to determine the value of ShippingDestination. So the first design is not 2NF.

3NF talks about "transitive dependency" - whether a column's value changes because the key changes or because a separate non-key column's value changes. I don't know enough about the actual business rules to say if the second design is in 3NF so I'll invent a scenario to demonstrate. Let's say a customer can belong to only one channel. As we move from one row in Order to the next we see that both CustomerNumber and Channel change value. However, Channel changes value not because there is a different SalesOrderNumber but because there is a different CustomerNumber. Channel varies by SalesOrderNumber by way of (i.e. transitively) CustomerNumber. In my made-up scenario Channel should be moved from Order to a new table (Customer) keyed by CustomerNumber.

To violate BCNF the table must have more than one candidate key which share at least one column. Neither design has this so, once in 3NF, the design is also in BCNF.

So yes, split the tables.