In my example given earlier which accounts for possible overlap in required fields, would your paragraph still apply?
e.g., I would probably need all the field information every time I access a source type's requireds so that would fall under your description; however, that would mean having to have repeated instances of, for example, "Author" fields under the different source types. Which becomes more problematic? The JOIN overhead or the repetition of similar data? Thank you, ~Jeffrey Santos On Wed, Sep 17, 2008 at 11:21 AM, Martin Gainty <[EMAIL PROTECTED]> wrote: > Good Morning > > Joins can easily result in FTS..so you might want to consider foreign key > to child tables > so assuming your starting with sourceRequireds as your topmost parent table > with foreign keys to (child) sourceInfo and sourceFields > keep in mind that later on de-normalising either child table could be > time-consuming > if you know know there exists a 1:1 relationship between sourceRequireds > and all columns in > any of the tables e.g. sourceFields > incorporate each of the sourceFields columns into parent sourceRequireds > will save > JOIN and any/all cartesian resultset processing later.. > > anyone ? > Martin > ______________________________________________ > Disclaimer and confidentiality note > Everything in this e-mail and any attachments relates to the official > business of Sender. This transmission is of a confidential nature and Sender > does not endorse distribution to any party other than intended recipient. > Sender does not necessarily endorse content contained within this > transmission. > > > > Date: Wed, 17 Sep 2008 09:57:05 -0400 > > From: [EMAIL PROTECTED] > > To: [EMAIL PROTECTED] > > Subject: Re: Appropriate Design > > CC: mysql@lists.mysql.com > > > > > On Wed, Sep 17, 2008 at 5:46 AM, Jeffrey Santos <[EMAIL PROTECTED]> > wrote: > > > > > I'm not sure if this is the right list to ask such a question; if not, > any > > > direction as to the appropriate forum would be much appreciated! > > > > > > This may be a simple question, but I'm still on the early stages of my > > > learning about databases. I'm developing an application that will > require > > > information from various sources. Since what might be considered > required > > > information about those sources will vary (occasionally dramatically), > I'm > > > unsure as to the correct design for the database tables. In other words > > > I'm > > > left with (I believe) two choices: > > > > > > 1. Create a source table that will contain, among other things, every > > > possible field for a source type and sort out what is considered > required > > > information at the application level. > > > > > > 2. Create a few source tables detailing the required information about > > > each source type. The only way I can think of doing this is something > > > along > > > the lines of: > > > > > > sourceFields: fieldId, fieldName, .... > > > sourceRequireds: reqId, typeId, fieldId, .... > > > sourceInfo: infoId, fieldId, sourceId, .... > > > > > > and then pull only the appropriate data from the database at query time > > > using JOINs. > > > > > > Which of these options (or a third I have no idea about!) would be > > > appropriate here? > > > > > > If possible, maybe a general "conventional wisdom" statement would > greatly > > > help my education on these matters! > > > > > > Thank you, > > > > > > Jeffrey Santos > > > > > > The second solution (multiple tables) is generally the only acceptable > > solution. > > > > The general rule (for one table or many) is whether the fields in the > single > > table are fixed in quantity or perhaps are repetitive enough to be > tedious > > or repetitive to manipulate. > > > > For example, assume you want a database of people in your state and the > cars > > they own. Clearly, since people vary widely in the number of cars they > own > > and since each car has similar data, puttling fields like car1vin, > car2vin, > > car3vin in the people table is the wrong solution. > > > > Your problem description is rather vague, but the way I'm reading it is > that > > your sources vary widely in the fields that need to be recorded. In that > > case, you'd generally want a table of sources (each instance of a > source), a > > table of fields (each field that MAY be associated with a source), and a > > table of sourcefields (the actual data for a given field with a given > > source). > > > > But you really need to solidify the description of your problem or have a > > simplest example so people can really help you. I don't fully understand > > your description. > > > > Dave. > > ------------------------------ > Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn > Now<http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns%21550F681DAD532637%215295.entry?ocid=TXT_TAGLM_WL_getmore_092008> >
