Hi David,

>I am looking for advice on the best way to design a database, or the way that best conforms to good database design principles. Here is my question:
>
>An existing application has the following two tables:
>
>orders (one), orderlines (many). They are related by the column cordernum.
>
>Now the client wants to add additional functionality. Given a certain set of conditions they want to group a given set of orders into a group. The conditions are based upon columns in the orders table, plus, possibly some other conditions not specified anywhere in the tables, but they don't care about the orderlines. I am fairly sure that it won't be the case that more than one order can be in the same group. The orders will be originally created WITHOUT the group. The grouping will be done in a batch, at some point after the orders are created.
>
>Design possibilities:
>
>DESIGN ONE
>One design possibility is to add a column to orders called cgroupnum, then add a table called ordergroups, with the field cgroupnum, and possibly some other columns pertaining to attributes that are unique to the group. Then, at the time of creating the group, insert a row into the ordergroups table, and fill the cgroupnum field in orders, for the orders that are in the group.

I would go with this, if order can belong to only one group

>DESIGN TWO
>The other possibility is to leave the structure of the orders table intact, and add two tables: ordergroupsheader and ordergroupsdetails, with ordergroupsheader having the column cgroupnum, and ordergroupsdetails having cordernum and cgroupnum.

I would go with this, if order can belong to more than one group

>I am inclined to think that DESIGN TWO is the better design, but I am not a normalization guru. If an order could be in more than one group, I know it would be necessary. But is it necessary or advisable if an order can be in only one group? Is it bad design to have a one to many relationship between ordergroups and orders, where not all the orders are grouped, and therefore the ordergroup column is blank, (or NULL?) in some orders.

>How can a distinction be made between orders where there is no groupnumber because either a) the grouping operation has not yet been performed at all on them or b) the grouping operation was performed, but the order(s) didn't need to be grouped?

It would be easy to do in 'design one' by assigning Null/empty value to group field initially and some other value (like "N/A") later if order doesn't belong to any group. With second design you'll have either add logical field to the order table to indicate if group where assign or not, or keep additional records in ordergroupsdetails with some special group code for orders that don't belong to any group.