Re: Three table database - period (?)

In article <3A76AF6D.70C2F781_at_axon.is>,   Jens <jens_at_axon.is> 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 Deja.com
http://www.deja.com/ Received on Sun Feb 11 2001 - 23:20:46 CET