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.