Teg schrieb:
Hello Marten,
Monday, January 16, 2006, 2:14:59 PM, you wrote:
To me duplicate entries or near duplicate entries in a table are a
no-no. Sounds to me like you're talking about inserting the object
multiple times in the same table each instance describing one
"property" of the object.
What you're suggesting sounds like
<id1><parent><child><property name><property value>
<id1><parent><child><property name><property value>
<id1><parent><child><property name><property value>
<id1><parent><child><property name><property value>
<id2><parent><child><property name><property value>
<id2><parent><child><property name><property value>
<id2><parent><child><property name><property value>
<id2><parent><child><property name><property value>
...
Which duplicates not only the ID but, the <parent> <child> entries
(from the original poster's requirements).
Whereas I'm suggesting
Table 1:
<id1><parent><child>
<id2><parent><child>
<id3><parent><child>
<id4><parent><child>
Table 2:
<id1><property name><property value>
<id1><property name><property value>
<id1><property name><property value>
<id2><property name><property value>
<id2><property name><property value>
<id3><property name><property value>
...
I don't belive you can do
<id1><parent><child><property name><property value><property
name><property value>...
Because you really have no idea how many properties there are going to
be. If you have one object with 2 properties and one with 100, you'll
have 200 columns of properties even for objects that only have 2
properties.
Now, if an object was ONLY properties then I agree, you could make a
table of only properties but, he's using the object table to define
some parent child hierarchy (It does sound like a sqliteized version
of XML).
Your suggestion is a possible extension - this table can be used to
add additional columns (used for optimistic locking - as an example).
Some futher thoughts about this:
The column <property value> is a simple varchar(x) column and the
software has to convert the attribute value in a possible and suitable
string representation.
An search index is set on this column (to search objects using some
attributes).
Therefore the conversion from the native datatype to the string
representation
has to be done carefully (as an example: how can one compare a negative
number against a positive number with different number of digits and
decimal points - when these numbers are only represented as a string ...).
The modelling of associations are just the matter of using one single
row for one object in an association (the property value contains the
object id referenced by this association).
If you have to model a :n association you may have multiple rows for
this object for one <property name>.
and so on, and so on .... please remember: it's not just a model
of persistency. Perhaps useless, perhaps usefull in special
situations.
Marten
