Re: My honours project - databases using dynamically attached entity-properties

On Wed, Mar 14, 2007 at 02:28:03PM +0000, Gregory Stark wrote:
> "David Fetter" <david(at)fetter(dot)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 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

--
mark(at)mielke(dot)cc / markm(at)ncf(dot)ca / markm(at)nortel(dot)com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | 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/