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 issue I have with the above is that it seems unnecessarily inefficient. Whenever mapping from a patient to a symptom, or a symptom to a patient, it requires searching indexes for three tables. Perhaps this would work well if there was heavy overlap of symptoms for different patients. For the cases I have hit this problem, however, there may be overlap, but it is not easy to detect, and even if it was detected, we would end with some sort of garbage collection requirements where symptoms are removed once all references to the symptoms are removed. The case most familiar to me, is a set of viewing preferences for web pages. Some users specify no preferences, while others have dozens of preferences. As I have no requirements to search for users with a particular preference, I chose to solve this by packing many of the preferences together into a TEXT field, and having the application pack/unpack the data. I still have tables that map object id to attribute/value, but they are used for the data that can require longer queries. Without clustering the data, searching for a dozen of these attributes requires either querying all attributes, where the attributes could be scattered throughout the table, or querying them one by one, which is worse. If there was an efficient way to do this for both of my use cases, I would be strongly tempted to use it. :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match