Spider to Anchor Buoy?

[xochi]

I'm several years into this rather large database project, it's working well, and I've finally gotten around to documentation. In the process, I'm realizing my relationship graph is a bit of a mess. Early on I tended to favor the "keep the # of TOs down, and relate everything to everything" design philosophy. Perhaps "philosophy" is too strong, I think I hadn't fully understood the "anchor-buoy" method yet, or understood the power of having multiple TOs for a single base table.

In any case, I'm now wondering if there is an easy (and safe) way to de-spider my relationship graph? My motivations are (A) easier documentation, and ( easier long term maintenance. In particular, I find that in the older parts of my solution, when you click a "related table" popup, you get 50-100 related tables. By contrast, as I learned FM better (having multiple TOs), my later designs are much cleaner -- click the related table popup and only a few, relevant TOs show up.

I'm very afraid, however, of breaking existing code. So I'm wondering if there is general method of converting spiderwebs to anchor-buoy design after the fact?

Here is a specific example:

Old way:

My "person" table is related to address, email, and phone tables, with multiple relationships (for work, home, fax, etc.). It's also related to my Salary table.

Person -> address, address home, address work, phone, phone home, phone work, salary

I'd rather have a bunch of functionally separate TOs:

Person to Address -> address, address home address work

Person to Phones -> phone, phone work, phone home

Person to Salary -> Salary

etc.

My main concern is, if I start creating new TOGs, is making sure that I catch ALL the place they are used, e.g. inside calculated fields, imports, etc.

[Vaughan]

I don't believe that changing to the A-B method will simplify anything... by my reading you'll end up with more TOs than you have now.

"I'm several years into this rather large database project, it's working well, and I've finally gotten around to documentation."

If it ain't broken... :)

[Dr. Evil]

I feel your pain, I also have a couple large databases that I have developed over several years.

I'm no expert, but I don't think there is any nifty way to just clean all that up without breaking some of your code and navigations.

Consider this tho...

...treat your solution as a classic car that has some real head turning potential. The only way to truly cherry a classic restoration is to take it down to the frame, paint and polish, and slowly put it back together.

I would do the same with your solution. Take one last HARD look at what you have built and make notes where you think you can clean some tables, TO's and TOG's. Like for example a contact should only have one table for line items, call it data. This Contacts Data table could store everything, ph, fx, em, url, address, etc.. . (Your solution may be like this, I don't know, just eg.)

If you are for the most part happy with the functionality of your current database, then I would use it as a guide to build a NEW database! Go for it! Create a new database and play around with some UI templates, and streamline your navigation. Take a look at some well built websites for inspiration. I recently rebuilt the navigation on my solution to mimic web site type navigation. The user loves it, and its more familiar to them.

Once you have a UI template designed with navigation icons, buttons, color scheme and etc... as well as an idea of how you are going to navigate... begin building your base Parent and Child tables. But this time around really plan on CLEANING up your relations and keeping it minimalistic. With each TO you create ask yourself, is this really necessary, is it too much info for user in the wrong place, does this already exist in the TOG or does it belong in a different TOG? Also do you have any naming conventions? Let say it... NAMING CONVENTIONS are very necessary when developing a large solution that you plan on using, adding to and developing for years to come. Use naming conventions for your Scripts, Value List and of course your TOG's. I use some thing like this for my TOG's.

Tables: Projects, Assignments, Products

PRJ~project -< PRJ~assignments

ASM~assignments -< ASM~products -< ASM~products_active

I use a three letter naming convention in every TOG and then follow it the name of the base table it is from, and then a unique label, like "Portal" or "Value", or "Active"

By taking a second in-depth look at your entire current solution from scratch, you will LEARN and knock off any rust you may have in any areas.

Once you have your new cherried out solution built, prepare yourself to import data from old and work out the bugs. You may need to create temp fields in new solution to put old info into and visa versa. Do a search on migrating and you should get some good info to read on this topic.

Good luck!

[xochi]

Thanks for the input. I was hoping that there was an easy way to do this.

A total rewrite is out of the question (this solution in fact is a total rewrite).

Maybe I'll take a look at the ugliest part of the graph and see if I can some minor cleanup -- I can probably do some tests on areas that are easy to test & debug, and get a feel for it.

If it ain't broke don't fix it might be the right idea... :)

[bcooney]

You don't have to delete any relationships, just redo what's really a mess using a-b. This way, you won't break anything.

[Fenton]

As bcooney says, you can rebuild parts and things should still work using a-b. Though you will likely need to add table occurrences (TOs) where needed to show data which is no longer connected. What we loosely call "relationships" are actually a combination of the originating table occurrence, the relationship path (lines), and the target table occurrence (and any table occurrences in between). If you delete the relationship path, move the TOs, then reestablish the path, everything should still work as it did (other than additions needed as mentioned). You can move whole groups of TOs at once.

The layouts are attached to their TO. Moving the TO does not break that connection. During the Go To Related Record operation, you can specify ANY layout attached to ANY of the base table's table occurrences. Hence you can jump anywhere you need to in the Relationship Graph, taking the found set with you. Of course, once there, the relational connections of that location apply.

You can even transfer the found set of your current location to another TO of the same table. This is known as the "save the found set" trick. It is done by Go To Related Record of your current TO, but specifying a layout of the another TO of the same base table which is not relationally connected.

Attached is a simple example, preserving then restoring the found set.

Find_wFS.fp7.zip

[Dr. Evil]

build a NEW file the way you want it. build as clean as you know how. do everything from scratch so you re-familiarize yourself/learn again what the guts of your solution are. Once done, you can import records from your old database to the new one. Then do all the testing/fine tuning/polishing on the new database file without the risk of loosing your vital data. Your old file will act kinda like a back up. Should be good for you and fun at the same time... the blueprint is done for you. By starting from scratch, you will be forced to ask yourself "Is this the best way?".. "Can I improve on this to be cleaner, faster and simple?"

Good luck my man!

[Dr. Evil]

You may like this or may not. But attached is how I prefer to layout my table graph occurrences. Different colors represent different function/areas. Dark blue is the may UI Table, light blue is the creation of children, yellow is used only for value list, etc....