Re: Normalisation versus optimisation

Please post real DDL instead of your own personal pseudo-code. It saves a lot of re-typing and wild guessing about, data types, constriants, etc. You might also want to start using the NCITS L8 Metadata naming conventions; things like "type_id" make no sense in terms of a data element name. Think about it -- a type is an attribute; why would an attribute have an identifier? It has a value!  What does "number_id" mean as part of a logical data model?

I think that you might be trying to do an OO model in SQL. This is a horrible mistake and things will fall apart in about a year. I just got thru working with a company that did this kind of thing ...

My guess is that you are dispatching products, one at a time and that for some reason you need to make a note about it. Here is my guess about a normalized schema for your database:

CREATE TABLE Products
(prod_type INTEGER NOT NULL,

 prod_variant INTEGER NOT NULL,
 prod_description VARCHAR(100) NOT NULL,  PRIMARY KEY (prod_type, prod_variant);

CREATE TABLE Dispatches
(dispatch_nbr INTEGER NOT NULL PRIMARY KEY,
 dispatch_description VARCHAR(100) NOT NULL,  prod_type INTEGER NOT NULL,
 prod_variant INTEGER NOT NULL,
 FOREIGN KEY (prod_type, prod_variant)
  REFERENCES Products (prod_type, prod_variant)); Received on Fri Oct 26 2001 - 19:24:36 CDT