Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Database design ? blank foreign keys?
Message
De
15/11/2002 17:25:17
 
 
À
Tous
Information générale
Forum:
Visual FoxPro
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
Titre:
Database design ? blank foreign keys?
Divers
Thread ID:
00723476
Message ID:
00723476
Vues:
47
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.

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 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?

TIA for reading this long post, sorry if it is too long!
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform