Here is a simplified data model I’d like to impliment based on normalized tables in a SQL database:
TABLE: Cases, FIELDS caseid, casename, casedescription, caseparents,
TABLE: Sources, FIELDS: sourceid, sourcename, sourcepath, sourcetype, sourcechecksum,
TABLE: Codes, FIELDS: codeid, codename, codetype, codedescription, codeparents,
TABLE: References, FIELDS caseid, sourceid, codeid, referencetype, referenceposition, referencememo,
Cases, Sources, and Codes contain unique (by name) entries). Reference links to these in a 1 to many relationship
When displaying References (say in a TABLE widget, the ‘caseid’ is used to fetch the ‘casename’, the ‘sourceid’ used to get the ‘sourcename’, the ‘codeid’ used to get the ‘codename’ from the respective tables (Cases, Sources, Codes).
It does not seem possible (at this time) to recreate this in Livecode Create. I would have expected Data Views to allow the joining of Tables (Collections) to allow me to create a View that is a References for binding to a Table widget.
Can you help on whether this data model IS possible in Create currently OR will be in the future?
I do realize, I could have a SINGLE collection with ALL the fields from Cases, Codes, Sources, and References all in it and create Data Views filtered to just unique Cases with its fields, another view for Codes, and so on (or at leasr I think I could - still have to try that). It seems terrible storage inefficent though, but maybe that is just by rational database design bias.