On Wednesday 2007-03-14 08:26, Csaba Nagy wrote:
> On Wed, 2007-03-14 at 16:08, mark@mark.mielke.cc wrote:
> > On Wed, Mar 14, 2007 at 02:28:03PM +0000, Gregory Stark wrote:
> > > "David Fetter" <david@fetter.org> writes:
> > > > CREATE TABLE symptom (
> > > > symptom_id SERIAL PRIMARY KEY, /* See above. */
> > > > ...
> > > > );
> > > >
> > > > CREATE TABLE patient_presents_with (
> > > > patient_id INTEGER NOT NULL REFERENCES patient(patient_id),
> > > > symptom_id INTEGER NOT NULL REFERENCES symptom(symptom_id),
> > > > UNIQUE(patient_id, symptom_id)
> > > > );
> > >
> > > I'm just glad I don't have your doctor. I hope mine doesn't think
> > > symptoms are all boolean values.
> >
> > Where is the boolean above? It is M:N, with each having whatever data
> > is required.
>
> The boolean is assumed in the symptoms table. In any case, even if it's
> not a boolean value, even if maybe the symptoms table is a complex one
> on it's own, it still is one single type for all symptoms of all
> patients. The real problem is that in some real world applications you
> have a mix of wildly varying types of attributes a user might want to
> use, and you can't know what those will be beforehand... the symptoms
> thing is simple to solve in the way David did it, but there really are
> other situations which a simple m:n can't easily cover. How would you
> handle a data base of user settings for 10K different applications and
> 100M different users where each application must be able to store it's
> own (type safe !!) settings in the same structure, and applications come
> and go with their own settings ? Come up with a good solution to this
> combined with queries like "give me all the users who have this set of
> settings set to these values" running fast, and then you're talking.
>
> Cheers,
> Csaba.
At that point traditional referential database design starts to break down.
If you need THAT MUCH flexibility it's probably time to look at network
databases, knowledge bases, extensible knowledge bases, and ad hoc knowledge
bases (OWL, RDF, etc). Flexibility, friendliness to marketeers or
accountants, extesiblity, none are really the strong points of relational
database. Databases as they exist today do best with finite domains that can
be formally organized.