Just for starters (I'm sure you'll get plenty of contributions to this
thread!)
Against this design:
(a) You won't be able to index the relations easily - this will be a major
database layer performance issue
(b) Constraints will be exceedingly difficult (although the architect is
probably performing constraints in the application layer)
(c) AttributeID violates first normal form (although the architect probably
doesn't care as he seems to be after an OO design rather than a relational
design)
(d) Your query will always scan the table because the wildcard search in the
join predicate is not useful as a search argument - would be the same
problem even if join predicate shifted to where clause btw.. This is a
related problem to (a)
For this design:
(a) Very (too) flexible - allows changes to be made at the application
easily without much consideration for impact on database (at the expense of
database performance & probably consistency also)
General comments - this design makes little sense to the relational DBA, as
it is so foreign to the concept of RDBMS. Even as an OODBMS it's a
questionable design as AttributeID could at least be normalised - possibly
even presented back to the application as this schema using a view if really
needed.
I have worked with similar projects, notably one where a heavy committment
to the application layer was made by the architect (using Java / Struts /
JDBC & Websphere technology). A similar RDBMS design was employed, although
a little more rationally - without your AttributeID fiasco. My instinctual
reaction was strongly against this at first, however I have to say that this
application performs fine (an international B2B website for a major US
supermarket brand) as the application layer has extensive caching technology
& the application layer has more knowledge about how / when to hit the
database. Sometimes there's more than meets the eye when you bump into one
of these type of designs. Sometimes the trade offs achieve significant goals
(mainly extensibility) that you might do well to seek out from the architect
of the system before standing your ground too strongly. Generally though -
if the architect can't point you to solid documentation that describes the
design / trade-offs, you should drive him / her for it in writing now
because if you don't, you'll be paying the price in trying to maintain that
system later..
HTH
Regards,
Greg Linwood
SQL Server MVP
"ViperDK (Daniel K.)" <vi**************@viperdk.dyndns.org> wrote in message
news:bp*************@news.t-online.com...
i've a database where relations are hold in a special way which the
project leaders think of as "performant and uncomplicated" but which is very
questionable to me:
------------------------------------------------
Table [Attributes]
Fields [AttributeID] and [AttributeText]
Table [Objects]
Fields object stuff.... and [AttributeIDs] (varchar with 0-20 ids usually)
in AttributeIDs there is a backslash separted list of Attribute-IDs like
'\34\12\2\78\'
so to get 20 object with a special attribute (which we need often) we do
SELECT TOP 20 *
FROM Objects
INNER JOIN Attributes
ON (Objects.AttributeIDs LIKE ('%\' + (CAST AttributeID AS varchar) +
'\%')) ORDER BY ObjectText
ps: to store data we need for communication we include a dozen of fields
in *every* table and its content makes about 100 bytes/record
------------------------------------------------
i would do this stuff with a table to store the object/attribute
correlations.
could someone tell me if that stuff makes any sense to an expert and how
to valuate it in regard of performance(we have big customers where that *is*
an issue), design, scalability, pragmatism and sense ;)
thanks in advance,
ViperDK
