Re: Use of Domain/Foreign Keys

Robert Owens wrote: > > This is a simple question -- I think. > > I have a table with about 20 columns. Ten of the columns are 2 character > codes that have associated tables with the valid codes and descriptions. > There are 300,000 records in the first table and from 10 to 100 codes in > each of the other tables. > > Should I set these up as domains? Yes, and you have by the sound of it. > Foreign key from my table to the associated tables? In theory, yes. However, 10 odd indexes on your main table could present a few performance issues. Are you sure your main table has been fully normalised? > How efficient is each solution? I am not sure of YOUR definition of domain then, because I see the above as a single solution. MY definition of a domain is "...the definition of the constraints on the values of attributes which often results in the creation of a new entity..." This implies that you could implement a solution using CHECK CONSTRAINTs if your valid values are few, which is possibly what you are referring to. But that would assume that the values were REALLY static. Otherwise changing valid values would require database changes. Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+