Storing attributes in long or blob columns implies that they cannot be
queried. There are also many limitations across different db vendors when
dealing with long data types (and blobs). For example, Oracle allows only
one long column per table. The VISITOR_ATTRS table in the example below has
a VALUE column, which I assume is a string (VARCHAR). I assume that numeric
and date types must be converted with possible loss of precision. I do not
know if typing is an issue or not. (From a look at your tables, I guess it
isn't) Queries are a little more difficult for the end-user. Taking your
example, to find all attributes where the name is 'Rafal' you would have to
query:
SELECT VALUE FROM VISITOR_ATTRS WHERE ATTR_TYPE_ID = 1 AND VALUE = 'Rafal'
which is not as simple or easy to understand as
SELECT VALUE FROM VISITOR_ATTRS WHERE NAME = 'Rafal'
yet, for flexibility, i have often used the attribute type approach with the
main drawback being typing issues and loss of pureness to the sql queries,
and an extra index to support both queries by name and name/value pairs. In
fact, I believe M$ Site Server uses a similiar approach with an
attribute-dictionary to store user-specific attributes.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Vladimir Sekissov
Sent: Friday, September 22, 2000 8:34 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Attributes vs OBJECTDATA
Why not simply leave mandatory fields in table and save optional in
longvarchar field (which all supported database has) in XML format
using for example Quick (http://www.jxml.com/quick)? As for me
structures where relations are replaced by node navigations are looking
elegant on paper but too hard to use and extend in SQL database.
fadkarpelevitch> My belief is that it makes much more sense to use a
structure like this for
fadkarpelevitch> the attrs:
fadkarpelevitch>
fadkarpelevitch> VISITOR_ATTR_TYPES
fadkarpelevitch> -------------------
fadkarpelevitch> ATTR_TYPE_ID TYPE_NAME
fadkarpelevitch> 1 NAME
fadkarpelevitch> 2 EMAIL
fadkarpelevitch> 3 SHOE SIZE
fadkarpelevitch> 4 BAD HABITS
fadkarpelevitch>
fadkarpelevitch> and store attr values in
fadkarpelevitch> VISITOR_ATTRS
fadkarpelevitch> -----------------------
fadkarpelevitch> VISITOR_ID ATTR_TYPE_ID VALUE
fadkarpelevitch> 1 1 Rafal
fadkarpelevitch> 1 2 [EMAIL PROTECTED]
fadkarpelevitch> 1 3 42
fadkarpelevitch> 2 1 Fedor
fadkarpelevitch> .....
fadkarpelevitch>
fadkarpelevitch> This way you have enough flexibility to add/remove various
attributes
fadkarpelevitch> without any java coding and schema changes by simply
manipulating entries in
fadkarpelevitch> VISITOR_ATTR_TYPES. But this way you do not depend on Java
to retrieve
fadkarpelevitch> values and you can run queries against any attr. There is a
question whether
fadkarpelevitch> it makes sense to move system-used attrs (uid, passwd,
email) into this
fadkarpelevitch> structure or leave in VISITOR table or, maybe, duplicate in
both.
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
