table occurrences, throwing me for a loop

[ispytodd]

When building a database schema, is it kosher to create loops using table occurrences?

Loops are illegal for good reason. It seems like it would like cheating to use a another T.O. to circumvent this restriction. I feel like I'm breaking some fundamental db design rules. But it's just so easy.

The reason I ask is that in a larger project I find myself relating almost every table to eachother, just because I can (and it makes some obscure layout easier to implement).

She the attached RG screen grab for a simple example.

[Accounts] -> [Locations] -> [Contacts]

(all are one -> many )

simple enough.. A contact is a grandchild of an Account.

However, suppose a situation comes up where I just want a list of all the contacts for an Account, regardless of the location. In haste I create another [Contacts] T.O. and relate it directly to [Accounts]. Now my RG looks exactly like the attached png file... For some reason this make me feel dirty.. ideas?

ps: I've been mindful of orphans. ie, whenever a contact is created, I populate the foreign key of its parent and grandparent.

Edited July 23, 2008 by Guest

[David Jondreau]

For some reason this make me feel dirty.. ideas?

Get over it? Take a look at the relationship graphy other solutions?

There's a lot of cool, free, professionally made stuff out there, which will show that having more than one TO of a table is 1000% acceptable and undeniably necessary in many solutions.

[comment]

I am not sure what you mean by "loops". Using multiple TO's of the same table is not circumventing any restrictions. You wouldn't be able to do much without them.

However, in your example the added TO of Contacts is unnecessary, since as you say a contact is a grandchild of an account - and you can get a list of grandchildren by referencing the grandchild TO directly.

I find myself relating almost every table to eachother, just because I can

That's not a smart choice, IMHO. At some point, relationships carry a price in terms of indexing, evaluation and caching - not to mention cluttering your graph.

[David Jondreau]

For what it's worth, John Howell at DevCon did a session on Anchor-Buoy and averred that the any performance issues as a result of a complex graph only exhibits itself on startup and for the vast majority of solutions, the effect is pretty small.

Edited July 23, 2008 by Guest

[ispytodd]

Or as cruel ex put it "build a bridge... get over it"

I'm all for T.O.'s. They're absolutely necessary, however I'm recalling some distant memories about database normalization.. 1nf, 2nf and all that jazz.

I suppose I'm asking if there is a fundamental problem having one table be both a parent AND a grandparent through the use of T.O.s.. (guess i figured out where that 'dirty' feeling came form)

appreciate all the replies..

-t

[Fitch]

FileMaker will prevent you from creating a loop in the relationship graph. The graph is not an ERD.

[comment]

I daresay you'll find varying opinions on this, for example:

http://www.nabble.com/forum/ViewPost.jtp?post=14177505&framed=y

These things are extremely difficult and time-consuming to test. I wouldn't take anybody's word for it - especially if I don't know how they tested and what they tested.

[Fitch]

There's no inherent problem with making one TO be both a parent and a grandparent of TOs based on any other table -- you can even be your own grandpa. However as Michael already noted, your example as stated doesn't require the extra TO, and it's best to keep your graph as clean as possible.

Database normalization has more to do with the underlying table structure, although elements of it, such as cascading deletes, and of course relationships of key fields, are implemented in the graph in FileMaker.

Because of the way FileMaker tightly ties together data, scripting, and presentation, you may find yourself e.g. setting up relationships from TO A to two occurrences of table B, the only difference being that one of the relationships allows record creation in B and the other doesn't.