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.
