Hi David, My project involves storing information that could have been gathered from wildly different sources. You can think of this part of the database as a sort of bibliography for the sources of that information. If I gathered information from a book, for instance, there are some generally required fields such as "Author," "Title," ... etc. A website, on the other hand, would also require fields such as "URL" and "Date Visited" and so on and so forth.
I hope that's more clear! ~Jeffrey Santos On Wed, Sep 17, 2008 at 9:57 AM, David Ashley <[EMAIL PROTECTED]> wrote: > 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. >
