Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Three table database - period (?)

Re: Three table database - period (?)

From: <>
Date: Sun, 11 Feb 2001 22:20:46 GMT
Message-ID: <96737n$mle$>

In article <>,
  Jens <> wrote:
> Hi
> I have been wondering about the merits / pitfalls of the
> following table structure:
> Entity(ID)
> EntityAttribute(EntityID, AttributeID)
> Attribute(ID, Type, EntityID, Number, String, Date, Blob,
> ...)
> This describes an entity that has some attributes linked to
> it through the EntityAttribute table. The attribute table's
> Type field describes which of the attribute fields is
> actually used.
> I don't seem to need any more tables as I can describe
> anything with these three (or can I?). I'm also concerned
> about speed and space consumption but the flexibility this
> seems to give me is tempting.
> You may even have a name for this 'little monster' :)
> Any thoughts?
> Thanks,
> Jens

We have implemented a large website which uses a data structure (in SQL Server 2K) in which the nature of a particular object is defined only at create ttime (ie there is no E/R model). We cannot create a true normalized structure of course and there are few PK/FK contraints possible.
An object is 'defined' (at create time) by a combination of tables used in its makeup, the links between them (held in relationship tables) and the nature and value of the data in particular text/character columns. This kind of structure allows us to create new 'objects' (ie things assembled from a collection of other 'things') on the fly...more or less ! It does mean that essentially the columns are untyped so this imposes the need to query these things as part of an 'ordinary' SQL query and, of course, there is a penalty. We have ingeniously used bitmasks and logical operations to enhance performance. We find that performance is acceptable (but not good -- yet) but that we have difficulty doing otherwise simple operations such as deletions and inserts and updates as an object's structure can he highly recursive (for want of a better word) and the code (in stored procs) required to do this and other tasks is convoluted and in many places requires dynamic SQL; never a favourite of mine. Whilst it is possible to do this I still have reservations about using relational systems for something they were never designed to do.

I think the comments on using ODBs are good but I wanted to state that at least one company has actually done this and done it successfully !

Gavin John Fowler
Tech Consultant
An Internet Company in UK

Sent via Received on Sun Feb 11 2001 - 16:20:46 CST

Original text of this message