Path: utzoo!censor!geac!becker!hybrid!scifi!bywater!uunet!seismo!beno.CSS.GOV From: jean@beno.CSS.GOV (Jean Anderson) Newsgroups: comp.databases Subject: Re: Join Contest Message-ID: <49004@seismo.CSS.GOV> Date: 19 Jul 90 03:36:10 GMT Sender: usenet@seismo.CSS.GOV Distribution: usa Organization: Center for Seismic Studies, Arlington, VA Lines: 54 In <12096@blia.BLI.COM>, miket@blia.BLI.COM (Mike Tossy): > seen enough poor database designs to lead me to believe that the 'state-of- > the-practice' of the average commercial programmer does not yet extend to > third normal form. I think it is the case that many of us inherit existing database designs. We try to make positive changes given opportune moments and new development. I feel fortunate that the database structure I inherited was for the most part intelligently designed. It was the ad hoc extensions to it that caused the most problems. In , cimshop!davidm@uunet.UU.NET (David S. Masterson): > If the two tables share the same primary key, are they not representative of > the same entity and, therefore, should be one table after normalization? Not always. For example, this is a seismic database with diverse entities which share the same primary key. For example, location hypotheses, magnitudes, and events really do share the same key. > To include the data in the primary table, the lookup data must be one-to-one > related with the primary key of the primary table, correct? I think you are essentially right. This case is actually one-to-many, but the main table's primary key is a composite key which when broken down contains the foreign key to the table in question (who's on first?). So this doesn't seem totally bizarre, there is a many to many relationship between seismic signals and location hypotheses. Multiple seismic stations contribute data for a given location hypothesis. An intermediate table keeps track of the associations: station info +----------+ +----------------------+ +--------------+ | seismic | <--->> | association of | <<---> | location | | signals | | signals and locations| | hypotheses | +----------+ +----------------------+ +--------------+ In this case, I put the station in the association table to avoid doing a lookup join to the signal table to retrieve assoc info by station. > I'm not sure whether your breaking the rules in the second place or going too > far in the first place. Bless you for saying that. :) It's sometimes difficult to make some folks realize that more is not necessarily better. - Jean Anderson SAIC Geophysics Division Most reliable return path: jean@seismo.css.gov +++++++++++++++++++++++++++++++++++++++++++++++++ ++ Any opinions are mine, not my employer's. ++ +++++++++++++++++++++++++++++++++++++++++++++++++