Relatively Speaking, 2010

I’ve been thinking a lot about relationships recently, but writing a ‘Definitive Guide’ seems such an enormous task that I’ve decided to make this a parts work, and focus on one area per blog.

Here’s a rough guide to where I’m going, though.

Relationships are one of the jewels in the DE-Ff crown. The simple connection of one table to another by common values yields many possibilities. Yet these have been little explored in writing, and there are myths from the DOS days still floating around.

Relational Groups

Think of relationships in two groups: structural and operational. Structural ones arise out of your entity relationship modelling. Operational ones, on the other hand, may appear to break modelling rules, but have many uses.

The Relationships form essentially consists of three (well, 3 ½) main areas: the two tables to be matched; the match fields; and the names.

(The half is referential integrity, which I’ll look at much later.)

You can link two different tables, or you can link the same table to itself.

The fields you specify either uniquely identify that table, or they don’t. If they do, that side by definition is the ‘one’ side. If not, it must be the ‘many’ side.

We’ll tackle the thorny issue of ‘fields that identify’ later.

There is one exception to the above – when there are NO match fields! I’ll definitely be exploring this more!

Name Your Relationship!

Finally, there is the relationship name. There are many different types of relationships, and the name should reflect this.

The name is an alias for how one side sees the other. You can think of it as being a property of that side, so that “Customer.SeeInvoices” (to borrow notation from object oriented programming) shows each customer has a collection of zero or more invoices.

My naming suggest the type of relationship, and I also try to make it as ‘English’ as possible. However, we are restricted by a maximum of twenty chars in DE 6.x, and since I have not yet confirmed that there are no issues with longer names in Ffenics, I still tend to stick to that limit. That can be tricky, of course, depending on the name and nature of the tables involved, so there may have to be a compromise for practical reasons.

By giving it a meaningful name, it should be quite clear what you can do with that relationship. ‘SeeInvoices’ means there may be a sub-form that displays all customer invoices. GetCustomer shows that the Invoice table can reference details in its one and only customer. Note that I use not only a prefix key word, but also the full name of the table (if the character count permits), and singular or plural.

One last comment: my preference is to put the ‘one’ side on the left and the ‘many’ on the right. This makes searching relationship records a lot easier!