Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Modelling objects with variable number of properties in an RDBMS

Re: Modelling objects with variable number of properties in an RDBMS

From: Bernard Peek <>
Date: Thu, 3 Nov 2005 00:52:17 +0000
Message-ID: <>

In message <>, writes

>I hope this is not a side-track here, but could you clarify this a bit?
> It sounds like you are saying you have a drug entity, some stable
>information about that drug, and then the results of multiple
>tests/experiments done against that drug (e.g. test1, test2, test3,
>test4,..., test750).
>Should these tests really be considered attributes? Wouldn't the act
>of presenting them as a two-tuple relation <test#, test result> simple
>be an wise step of normalization - not an example of an EAV
>decomposition? Am I totally missing the boat on this?

I don't think so.

If the tests are the same on each drug then it would probably be best to have an entity for the drug, an entity for the tests and a link entity showing the result of each test for each drug. Even if the tests are different for each drug it's still a valid structure. It avoids having too many attributes in one entity.

There's a pragmatic problem in implementing the system like this if there are a large number of different tests. You could enter a free-text name for the test and an autonumber to be used as a foreign key. The problem then is making sure that nobody types the same test name with variant spellings or punctuation. That's always a problem with autonumbers.

To minimise the risk of that I would try to define a number of unambiguous test categories. Perhaps based on the technology used; HPLC, TLC etc. Then users can pull down a reasonably short picklist of existing tests and only need to create new one if the test isn't already in the list.

Bernard Peek
London, UK. DBA, Manager, Trainer & Author.
Received on Wed Nov 02 2005 - 18:52:17 CST

Original text of this message