Does Building a Normalized Structure Create TO Galore?

[dmontano]

I am really beginning to wonder if I am building my relationship graph the hard way. First, I am building all my tables (I believe) in third normal form. I can see the value in doing this and have been able to get any related tables' data as needed. That is not to say that I have a fully functional interface - but I can create, modify, and delete as needed. My issue is the way I have to duplicate the TOs in order to display this related information in say - a portal.

For example, if I have a record in a table like this:

John Doe, Los Angeles, California

…and want to show all the PEOPLE in the database on the same layout (including the persons' record I am sitting on), I create a self-join relationship to a duplicated copy of the PEOPLE table occurrence and use "x" join. TO would be named: "PEOPLE_ALL" and related by its keys.

Works fine. Now, in order to display the city and state information which are in their own dedicated tables, I have to duplicate the CITIES and STATES table occurrence to create CITIES_ALL, STATES_ALL table occurrences and relate those to the "PEOPLE_ALL" TO - even though the "base" TOs (PEOPLE, CITIES, STATES) were related to begin with.

I have noticed this pattern all over the place whenever I need to display related data in portals. To further my doubts, while in drop down mode to select a related table to assign what field the portal will display, the base table - such as CITIES - is in the related TO list and allows me to select it to display in the portals field. However, it does not display accurately and will repeat its values.

Once I learned that I had to duplicate the base TO (CITIES) and relate it to the portal's TO in order to get the accurate information - I have just settled in and do that now as normal course. But this seems like redundant relationship building and odd that FM allows me to select a related TO but not display that information accurately.

If someone can confirm that this is "just the way it is" - then I will continue down this path building TOs galore. But if I have missed the boat on understanding how to do it the right way - please steer me right.

Thanks

[Søren Dyhr]

Your post seems almost like an echo of these I read a few days ago:

1) The higher the level of normalization, the greater the number of tables in the database;

2) The greater the number of tables in the database, the more joins are necessary for data manipulation;

3) Joins slow performance;

4)Denormalization reduces the number of tables and, therefore, the reliance on joins, which speeds up performance.

...from: http://www.information-management.com/issues/20020601/5251-1.html

But exactly this problem of yours:

the base table - such as CITIES - is in the related TO list and allows me to select it to display in the portals field. However, it does not display accurately and will repeat its values.

Is precisely what this video deals with from 0:40 (hour) and onwards:

http://www.filemakermagazine.com/videos/graph-rules-four-rules-to-remember.html

So yes it's going to be a galore!

Above is something I'm nursing for the moment, to manage due/overdue ... when a payment is arriving to establish which claim it belongs to is some filtering required to get rid of the paid ones as well as those not up for payment yet. And here do I wish the two aspects should reside on a tabbed layout, hence the use of only one TOG.

--sd

[comment]

Now, in order to display the city and state information which are in their own dedicated tables, I have to duplicate the CITIES and STATES table occurrence

Or use calculation fields in PEOPLE to pull the the related data to be displayed.

[dmontano]

Thanks Soren and Comment,

Soren, the video help confirm what I was experiencing. As for Fabian's article - I have read that on his site. I have been pretty much sold on normalizing. Whether it is practical or not is something I will have to learn for myself. I have not seen any performance issues - but then again, what do I know!

As for my suspicions in my original post - what you have said confirms how I have been working - so I guess I do not have to unravel what I have created this far.

The calculation is something I had not thought of - but have just tried. This is an unstored calculation - but do not know if I NEED it stored or not. I am thinking I do not since the portal is just displaying it. However, in the case where one might want to enter data in that portals field - that would not work - I think.

I tried an auto-enter calc and it works - but then I have it stored and updating the base table will not reflect correctly and I would be storing a "copy" which is not something I think I want to do? I recall reading somewhere of how to make an auto-enter calc refresh - but am not familiar with the technique - nor any downsides it may have. Something like it can replace the use of lookups in large part. Have I got that correct?

Thank you both for your help.

[Søren Dyhr]

I toyed a little with Comment's suggestion, and my take here here is it has to unstored calc'fields and not anything autoenter. But when tunneling in pre fm7 mode, must more care be put into to the integrity...

This made me exploit the Other... feature in Popup menus - since we now can use event triggers to do some actions on changes of such popups, how do we catch what's entered and create records accordingly while preserving integrity? I enclose a suggestion!

In the Galore model is it completely script-less to facilitate the Cartesian product portal with Popup menu's for writing the correct ID to get the values from the seemingly redundant TO'es.

--sd

HardlyGalore.zip

[comment]

Auto-entered calculations are definitely NOT something I would recommend for this. Usually, an x self-join would be used for navigation only, not for data entry. In such case, an extra calculation field (unstored) might be a better "investment" than an extra TO (esp. since a single calc can eliminate several TO's).

If you have a value list, then Søren's idea of formatting the foreign key as a pop-up could also be useful - though I would make the field non-enterable and not require any script triggers.

In the end, it's a balance act between what you need and what are you willing to pay for it.

[Fenton]

A small addition. When you tunnel data with calculations, pay attention to the "Evaluate this calculation in the context of:" with the TO. Usually the default one is what you want (but not always).

[dmontano]

Thanks to all three...

I have attached a normalized version of what Soren posted to show what I typically do when the data is normalized and in many separate tables. Is what I am doing "sound"? Is it correct? I can see the method that Comment suggested with calculation - I tried it and it works.

So is it safe to say that if the data is for DISPLAY only - and not data entry - then calculations are just as "sound" and viable as what I have been doing with multiple TOs? Is there any downside to the calculation method to display?

If so, is it safe to assume that if I need to create records in a portal (granted - not an "x" join portal) and the data is in many tables away, but related - I will have to use multiple TOs to set the keys AND display the data - similar to the structure I built in the file posted?

Thanks

[Søren Dyhr]

I will have to use multiple TOs to set the keys AND display the data - similar to the structure I built in the file posted?

We're talking 3rd normal form here, which I forgot ... I need to think here!

--sd

[comment]

is it safe to assume that if I need to create records in a portal (granted - not an "x" join portal) and the data is in many tables away, but related - I will have to use multiple TOs to set the keys AND display the data - similar to the structure I built in the file posted?

No, I don't think so. For one thing, you won't be able to create RELATED records in the remote tables through the portal. For another, in your example, all you need to enter in the portal is the city FK - which is in the same TO as the portal's. Once you do that, the relationship to the city/state/county is established, using the base TOG - and the related values can be displayed using calculation fields.

[dmontano]

Hi Comment,

I am so glad you have used that example of selecting the city and the establishment of State and Country will exist (if previously associated in that manner). For example, if you have a city that you have never entered into the system - it would not have a State, not Country. I have found myself in this situation many times with a different topic - not cities and states, but with categorizing an entity.

I stayed up late trying to build and example file - and I just got too tired (2:00 AM) to finish and post so you can see what I am referring to. I will try to do that this weekend because - as I mentioned before - this has been a real mind bender for a long time for me.

[dmontano]

Still working on building that file - but in the meantime, I believe some things are starting to get a little clearer to my problem - and maybe a solution to it might be answered by someone on this forum.

Using the fact that selecting the lowest level record in a hierarchy will result in the DB understanding who its parent, grandparent, etc. is and therefore display those relationships is a given. However, getting to the last child record is the problem and is what I am struggling with.

Let's say there are four cities in the database with the same name, but each are from different states. You can not select the correct city unless you are selecting it in the proper context, eg. state > then city. So even though selecting the "correct" city will determine the parent, the city is dependent on the user selecting the state first.

So, I think the overall solution to my problem (which is outside of the state, city subject area - but I used it to explain the situation) is to build an interface where the user selects the top node, then the next lower node, and so forth to arrive at the proper list in which to choose from.

Since this is what I want to be able to build, can this be done when the tables are built using a recursive structure such as a self-join with a parent_fk field in the table? Can it be built when the table structure is a many-to-many recursive structure where an interesction table is built that holds the parent_fk AND the child_fk?

I think I have narrowed down the major missing element in what I am trying to provide - I hope this makes sense. I see two aspects to what I am trying to provide: 1) an interface that allows the correct "city" to be chosen based upon a user interface that has drop-downs (or pop-ups) that drill down to the list of appropriate choices; and 2) build the same type of interface where child records can be created - this would be used when the User is defining the hierarchy. The second aspects comes into play in situations such as when a User needs to be able to decide for themselves how they may want to categorize or classify a product and will need to build the "name" of the category in which to associate with a particular parent.

Which leads to my last observation: in many cases there will not be a set-in-stone number of levels. The recursive structure will not have a limit - and should not in some cases. Therefore, the recursive self-join in a one-to-many, or the two table recursive structure for a many-to-many seems like the best way to build the tables to account for the flexibility that Users would need to build out their categorization schemes to any depth they saw fit.

If this is true, doesn't that put a User interfaces that allows drop-down conditional selection impossible? Doesn't the conditional value list need to know all of the tables in which to condition for? Or all the levels?

Any insight is truly helpful.

[comment]

the user selects the top node, then the next lower node, and so forth to arrive at the proper list in which to choose from.

Since this is what I want to be able to build, can this be done when the tables are built using a recursive structure

Of course it can be done. At its simplest, user starts at ANY node and has the options to (1) go one level up (GTRR from parent); (2) one level down (GTRR from child), or (3) choose the current node. Very much like selecting a file in "Open File" dialog.

Another option is to use a hierarchical portal and expand the desired nodes until you get to the one you want.

The same holds true for your other question. No, you cannot use a fixed number of drop-downs. You must use a similar process to allow user to select the parent for the new node (or for any node, if it comes to that).

[dmontano]

Thanks Comment,

I found an old file that Fenton and you had helped me with some time ago - but I could not even come close to understanding it at the time. It has 4 tables: Categories, Subcategories, Types, and Subtypes all related with Categories being the top level - Subtypes being the lowest. Fenton went to the trouble of building out the "filtering" relationships (I think that would be the correct term) to show how the selection process is accomplished.

In his example, there was another table "equipment" that was holding records for each equipment item and I wanted to categorize each item with values from one or more of the categorization tables. In the equipment table, Fenton placed one "fk" for each of the categorization tables.

I can now see that my old request for help on that subject shows I had a rigid structure for the hierarchy and the tables were representing the levels. I might as well work in that file to see how the drop down selection works in that structure first, then I will have to figure out how to do the same type of selection interface for the recursive 1 table self-join, the the 2 table method that allows a many-to-many recursive relationship.

I am starting to gravitate towards the self-join for 1 to many recursion scenarios, and the 2 tables for the many-to-many recursions. Does anyone think this is generally a good approach to allow flexibility? Or do these structure represent a major headache that I am unaware of?

Comment, I will definitely look into the gtrr portal and the hierarchical portal as you have suggested. I think I read Mikhail's paper on the topic and knew it was way over my head - so I am looking for a solution that I can understand and build - but also goes along with the table structure of the self-join, or the 2 table many-to-many. Not sure his method applies - I do not have his paper right now to refer to.

Thanks

Edited September 1, 2009 by Guest