On Wed, 2007-03-14 at 16:08, [EMAIL PROTECTED] wrote: > On Wed, Mar 14, 2007 at 02:28:03PM +0000, Gregory Stark wrote: > > "David Fetter" <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate