3

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:

Order table

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

Order table with OrderDetail

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 
  • 1
    Hi--you can answer your own question by simply trying to create an order that has multiple parts. Your "current" model won't work because you can only have one part per order. So you certainly must have a separate table if there can be more than one part per order. – Tony Hinkle May 24 at 17:56
  • Right--this is a ubiquitous pattern that you can find in many sample databases. In the OrderDetail column, the SalesOrderNumber will be a foreign key that references the SalesOrderNumber in the Order table. PartID will be a foreign key to PartID in the Parts table. See sqlservertutorial.net/sql-server-sample-database – Tony Hinkle May 24 at 18:15
  • Ok, I couldn't get the formatting correct so I added in the post an example of how it would appear in the Order table currently. I'm wondering if it is poor design to leave it this way. Does this violate a normal form rule? – ahh_real_numbers May 24 at 18:23
  • The 1st design violates 2NF. The second is good. – ypercubeᵀᴹ May 24 at 18:31
  • Can you explain how it violates 2NF? Doesn't OrderQuantity depend on both PartID and SalesOrderNumber? A PartID can be associated with many SalesOrderNumbers, so it will also come in various quantities. – ahh_real_numbers May 24 at 18:50
2

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.

1

The answer is simple. Yes! Probably this mean changing the apps as well.

The second design is much better. You do not repeat Order Date, Due Date, etc. But, consider changing the primary key in OrderDetail to be an identity. Even more, in the Order table, I will place identity as a primary key as well. Of course, such column should exist in the Order Detail table as a foreign key. I'm not sure why do you think that an order should have only once part id? If your business suggests you, that ok. But IMO, everything might happen, so this could be a limitation in the future.

  • 1
    I'm not exactly sure what you mean by, "I'm not sure why do you think that an order should have only once part id?" An order can have many parts in it, with each part having a quantity >= 1. I'll update my example in the post above to make this more clear. – ahh_real_numbers May 24 at 19:11
  • During 25 years of working in the industry I saw many orders that have part id 1 in the first line. After that, in the second line again part id 1 with some extra conditions. So, I would like to say that combination of order id + part id usually is not unique. – Darko Martinovic May 24 at 19:14
  • I see. Well OrderDate, DueDate, CustomerNumber, ShippingDestination, PurchaseOrder, GroupNumber and Channel should all be dependent on SalesOrderNumber only, which I realize might answer my question for me. – ahh_real_numbers May 24 at 19:17
  • 2
    @ahh_real_numbers, Apart from what [@Dark] said, you should keep Identity PK in each of the table which will be reference in all table.Say OrderID should be reference in Order detail table or in any other table.SalesOrderNumber is for displaying purpose like in Bill,Report etc.This is good practice. – KumarHarsh May 27 at 8:53
  • @Kumar, Ok, so even though SalesOrderNumber is always a unique string of numbers/letters, you would still create a field OrderID which is the PK and a surrogate for SalesOrderNumber? Would you do the same for the OrderDetail table, creating a OrderDetailID surrogate PK for (OrderID, PartID)? – ahh_real_numbers May 28 at 22:53
1

Normalization is already well explained.

Clustered Index should be,

  • Ever Increasing
  • Narrow
  • Most frequently use in Predicate
  • SelectivityIt should be selective enough.If it is use in where clause it should return 1 or 2 rows . In other word it should return 1% or 2 % of total rows.

    Order TABLE

    • OrderID int identity(1,1) PK
    • SalesOrderNumber varchar(15) Not Null

      OrderDetail TABLE

    • OrderDetailid int identity(1,1)

    • OrderID int Not nuill FK clustered index

In other table also refer Orderid as FK instead of SalesOrederNumber. Internally handle everything with OrderID.

SalesOrderNumber is there to serve Biz. Requirement.

Even in Order detail Table most of the rows will be retrieve using OrderID

So Orderid should be CLUSTERED INDEX. Also OrderID is ever increasing so CLUSTERED INDEX will be auto sorted.

Though OrderDetailid is more Selective than OrderID .Still Orderid is Selective enough beside other advantages.

So having OrderID as a clustered index in the OrderDetail table will keep each order ordered (keep each order arranged sequentially in the OrderDetail table.)

Yes.

Does arranging the OrderDetail table in this way help with performance? Can you explain what the benefit of clustering around OrderID is as opposed to OrderDetailID?

See most of your important queries will retrieve records from OrderDetail table using OrderID only. Isn't it ? Most of your queries will use OrderID in predicate.So Orderid is ideal choice for Clustered Index.

But there will be many rows for each OrderID since an order is composed of many parts.

I tried to explain about Selectivity of Index above.Each Orderid can have maximum how many parts ?5,10,50 ?

Best way is to populate Real Table with millions of records and Test with one Real query.You can provide real table Structure and their data type.

  • So having OrderID as a clustered index in the OrderDetail table will keep each order ordered (keep each order arranged sequentially in the OrderDetail table.) But there will be many rows for each OrderID since an order is composed of many parts. Does arranging the OrderDetail table in this way help with performance? Can you explain what the benefit of clustering around OrderID is as opposed to OrderDetailID? – ahh_real_numbers May 30 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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