Hi OC,
I had to deal with similar situation in the past for online product catalog and
I used these two way of storing the data successfully :
1- Store additional attributes in a serialized map structure (json, serialized
php array, serialized java objects or property list).
This allows to store anything that can be serialized and fit inside the blob or
big text column. The price to pay is in search, you cannot search with the
database or use index. This structure is the one to use when the data structure
is completely custom from item to item. At this time, I would use a document
based database like mongoDB with use this type of storage natively. With WO,
you can use a document database for the dynamic documents and a relational
database for the transactional datas. I would not recommend using a document
database for transactional operations, they are not designed for that.
2- Store the additional attributes in an dedicated entity with a commun
dictionary of attribute definitions. The attribute definition define the
attribute display name, data type, unit of measure (if required). You may have
a single list of definition or a list per item category for example. When the
user want to add an attribute to an item, he select the attribute from a list
and add it to the item. The form is dynamicaly constructed with correct display
and data validation based on the attribute definition. Attribute values are
stored in a small entity linked to an item and an attribute description. You
can index the value columns and do fast search in the database. By adding some
utility method to your Item entity, you can have a dictionary like behaviour
for your additional attribute values.
Entities required:
AttributeDefinition <-->> ItemValue <<--> Item
----------------------- ---------------- -------------
id id id
name itemID name
type attributeDefinitionID ...
... intValue
decimalValue
textValue
For optimal queries, you will need to use the ERXExistsQualifier presented last
year. I implemented this structure one time in WO to read data managed by a PHP
app but few times in PHP for product catalog and dynamic forms like
http://vizu.ca
Regards,
Samuel
Sample of utility methods for a dynamic form implementation (code is a unique
never modified, even when a new version of the form is created identifier for a
question):
public ClientQuestionReponse reponseForQuestionCode(String code) {
EOQualifier qualifier =
ClientQuestionReponse.QUESTION.dot(Question.CODE).eq(code);
NSArray<ClientQuestionReponse> reponses = reponses(qualifier);
if (reponses.count() == 0) {
return null;
}
return reponses.objectAtIndex(0);
}
public String textReponseForQuestionCode(String code) {
ClientQuestionReponse reponse = reponseForQuestionCode(code);
if (reponse == null) {
return null;
}
return reponse.texte();
}
public BigDecimal nombreReponseForQuestionCode(String code) {
ClientQuestionReponse reponse = reponseForQuestionCode(code);
if (reponse == null) {
return null;
}
return reponse.nombre();
}
public NSTimestamp dateReponseForQuestionCode(String code) {
ClientQuestionReponse reponse = reponseForQuestionCode(code);
if (reponse == null) {
return null;
}
return reponse.date();
}
> Le 2015-01-13 à 18:55, Ramsey Gurley <[email protected]> a écrit :
>
>
> On Jan 13, 2015, at 8:26 AM, OC <[email protected]> wrote:
>>
>> Well the gist is that some of my EOs have (along with normal ones) a set of
>> user-defined attributes: the user can open an appripriate editor and set up
>> something like "my auction will, along with the attributes which the
>> application itself defines, also three VARCHAR attributes named 'foo',
>> 'bar', and 'bax'; they will have four DECIMAL attributes named so-and-so,
>> etc etc.". These things are stored in the database, and the application then
>> allows to use the dynamic attributes the very same way one can use static
>> ones (i.e., display them in forms/tables, edit their contents, filter by
>> them, etc.)
>>
>> Originally, I have solved this through a BLOB, which contains a serialized
>> NSDictionary, which contains all the dynamic attributes by-name.
>>
>> That works reasonably well, but we bumped into grave efficiency problem
>> filtering such tables: to fetch only items whose attribute 'foo' matches
>> 'John*', we can't do SQL SELECT; instead we have to fetch the whole table,
>> decode all the BLOBs, and filter by the results. That proved a show-stopper.
>>
>> Thus, now I have to implement all those dynamic attributes as table columns,
>> so that I can (e.g.) use fetch qualifiers for them.
>>
>> Thanks and all the best,
>> OC
>
> So the plan is to have users executing SQL DDLs on the live production
> database?
>
> You may want to consider something like Postgresql with JSON datatypes
>
> http://www.pgcon.org/2014/schedule/attachments/328_9.4json.pdf
>
> Or if you just love (are stuck with) Frontbase, running an instance of
> CouchDB and passing data and queries to it via http. Either option seems much
> safer to me than having end users running alter table statements on the
> production database.
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list ([email protected])
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
>
> This email sent to [email protected]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
