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

In message <1130965639.482009.182400_at_g49g2000cwa.googlegroups.com>, casey.kirkpatrick_at_gmail.com 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.