C++/OOP vs. Database/Relationship Design

[kennedy]

I've been doing Object-Oriented Program for some 15 years. I've designed some large, successful object models... each geared to mostly be run in-memory.

Now I am using FrameMaker and designing a medium-sized relational database.

My questions for you who are more experienced than I:

1) When making the choice between N fields, a repeating field, or a separate related file, how does that differ from the similar choice in OOP? Are there costs associated with separate files that are bigger than with separate object types? Are the costs of indexing and doing finds very different?

2) In OOP, objects can refer to others very directly and very cheaply (via an object pointer, which serves as a unique ID that is zero-cost to follow). In relational databases, there is no equivalent to a pointer. Right? You can define unique IDs for each record and index on those to make the lookup fast, but the cost is non-zero. How much is the cost, relatively speaking? Should I be avoiding separate files where I can to prevent slow-downs due to excessive lookups?

3) In OOP, when one object has a list of sub-objects, you can easily keep pointers to them in a linked list, making it nearly zero-cost to navigate that sublist. In relational databases, there is no equivalent to that linked list of pointers. Right? Instead, you will always have to do a lookup on your own ID in the related file to collect the list of records representing those sub-objects. Indexing makes that much faster, but nowhere near the efficiency of a linked list. Again, because of this should I be laying subobjects into the parent object where possible (where I know the max number) to avoid the expense of those lookups?

4) What other design guidelines or rules of thumb should I follow when designing a relational database in FrameMaker vs. an in-memory object design in C++?

Thanks!

[CobaltSky]

I'm guessing you mean FileMaker, not FrameMaker (this is after all, the FileMaker forum...?!). Others may provide a more complete answer to your questions.

Comparisons with object model concepts are perhaps more directly applicable to aspects of the FileMaker architecture which relate to defining layout objects to point to data and code elements (eg attaching buttons to scripts, portals to relationships etc) and to defining entity relationships for commands within the ScriptMaker.

As you've gathered, the relational data model is index driven, and there is therefore always a cost, albeit relatively small. As a consequence, complex relational modeling introduces a performance overhead, and this is inescapable - it comes with the territory with relational vs object underpinnings.

That said, it is worth noting that cpu cycles are not at such a premium these days, and unless you are writing for antiquated hardware, there is generally some 'room to move'. Strategies such as laying in sub-objects within the parent tend to mitigate against the flexibility of the relational model, since the proper location of theose sub-objects is at/with their definitional source within the model. There is a risk of making a small gain in efficiency but trading it for loss of flexiblity or (worst case scenario) data purity.

My advice is to embrace the relational model where data structures in FileMaker are concerned. It is, after all, one of the strengths of the environment, and gain the advantages that has to offer, until/unless performance becomes a point at issue in a given platform or implementation, but to apply concepts associated with object models to other aspects of the design process (eg layout and scripting design). That way you stand to gain the best of both approaches without getting side-effects from conflicting design principles.

[BobWeaver]

OOP structure is a lot more flexible than relational databases so there will won't necessarily be a corresponding database item for every OOP concept.

1. Generally try to avoid repeating fields. While they may appear to be similar to arrays, you can't index a value in a repeating field with a variable, just with constants, which makes them rather unworkable for much of anything. Better to use a related file anywhere you would normally consider using an array. Any costs associated with using extra files are generally more than offset by functionality and maintainability. Speed shouldn't be much of an issue.

2. Filemaker allows you to have relationships within a single file so that you can link records together. You can traverse the links easily in a Filemaker script. However, you are limited in your layouts to displaying data only from the current record or a maximum of one relationship away, without resorting to some scripting to build up a display list.

Also, while recursive scripts are theoretically possible they are not practical. That puts limits on what you can do with non-linear data structures.

3. You can set up a field of categories that tracks what set a particular record belongs to. You can put multiple values in the field, if the record belongs to multiple sets. Filemaker can do finds very quickly and efficiently because of indexing.

4. Get a good book. The one by Colombre and Price 'Special Edition, Using Filemaker Pro' is good.