Starting Over - Insight Needed

[jtice]

I have a Contact Estimate Invoice solution that I began years ago with the earliest versions of Filemaker. I evolved it along with Filemaker very well up to a point, but now I need to back up, rebuild, and get it right. Fortunately, I have a solid set of data files to work with. So what I am looking for is some discussion or recommendations on how best to set up the structure in a systematic way, powerful and flexible but no more complex than need be. Since this is probably the most common type of solution I'm sure there must be some recognized best practices for organizing and creating the ERD. If there is a great open solution I can use as an example, please point me to it. I realize there isn't much detail in this description but that's really where I am right now- backing away from a solution that has grown too complex and disorganized and looking for a model that will give me a fresh start on a solid foundation.

One of my main frustrations is how to structure Company and Contact relationships. Right now I have Company as the parent of Contact (because the norm is a single office with multiple contacts), but then I run into situations where a Company has offices in several cities. So in that case addresses need to associated with Contact rather than Company. Company is an account so it would not be appropriate to demote it to an attribute of a contact... Anybody have any wisdom as to how to make this all work? Thanks.

[Matthew F]

I'm not a pro developer, but if you want a polished product, then perhaps you should consider hiring one. They are likely to have solutions for this problem that could customized to your needs.

Regarding your relationship issue it seems like you should have separate tables for accounts, people and addresses. Under addresses you have different types such as billing addresses, business addresses, and personnel addresses - which could be so designated by a checkbox. You could have multiple instances of your address table in your relationship graph in order to link up to accounts, people, or businesses.

[jtice]

Thanks for the tips mfero- I have a usable solution as it is and just looking for some fresh insight. I would simply like to deconstruct a solid solution and learn from it rather than trying to reinvent it from scratch. I do have those entities in separate tables so it's mostly a matter of how to set up the relationships and create a smooth user interface.

[Ron Cates]

Perhaps a new table in the hierarchy. I was thinking Companies --> Locations --> Contacts. Just a thought.

[dansmith65]

Perhaps a new table in the hierarchy. I was thinking Companies --> Locations --> Contacts. Just a thought.

I second this idea. Each Company record would have to have at least one 'Location' record, then all Contacts would be related to the appropriate Location.

If you are wanting some ideas on how to setup your TO's, then this article by Ray Cologon is good:

http://www.google.com/search?q=approaches_to_graph_modeling_en.pdf (it should be the first link)

If you are wanting some ideas on coding standards, here are some links:

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

http://www.linearblue.com/standards_fields.php

http://jonathanstark.com/filemaker-development-methodologies.php

http://www.filemakerdesign.com/content/filemaker-naming-conventions

http://coresolutions.ca/importance-naming-conventions

(I have downloaded a PDF from myFMbutler, but can't find it on their website now)

Sample files:

http://www.fmstartingpoint.com/

http://seedcode.com/

[jtice]

Thanks for the replies and links to the PDFs. I had a few of them and added the rest to my resource folder.

I do have a separate table for Addresses, which I believe would be equivalent to the Locations table you all suggested. The sticky part lies in how to make it interact with the Company and Contact tables. I have two instances in the graph, one connected directly to Company by CompanyID and another connected through Contact as such... Company>Address and Company>Contact>Address. Company is the anchor in the anchor-bouy. So when adding a new address it ends up being a child of Contact by ContactID. This presents a situation where multiples of the same address end up in the Address table, similar to the way it would happen in the Phone table if phone numbers were not inherently unique. If the data is going to be duplicated there is no advantage over keeping it in Contacts as an attribute (which is not what I want). So I think I need to flip it around so that each address or location is unique and the AddressID is located in Contacts (which should have only one address). Perhaps I should delete the Company>Contacts>Address instance and script the interaction so that the user can set an existing AddressID in Contacts, or create a new record, setting both CompanyID in Address and AddressID in Contact.

I think I see what you all were thinking in suggesting Company>Location>Contact as that switches the child/parent relationship, but... well, actually that's looking pretty smooth. That may be it- just switch the order.

Any suggestions or ideas on how to set up the selection and new record interaction... a highlighted portal for selection with a new record button below it?

[Ron Cates]

I don't believe your addresses table can be substituted for a location table. Addresses is a child of contacts whereas locations must be a parent of contacts. Also, I can see a situation where a contact may have a billing and a shipping address which would be 2 child records of a contact. I think what may be more accurate would be to create a location table and make addresses a child of location perhaps. The point is as it stands your address table is not the same as a locations table.

[jtice]

Yes, I see that. I'm thinking that addresses would then be attributes of Location (contained directly in rather than a child of). I don't know of any instances where I'd need more than a billing and shipping address. I supposed theoretically you could say that it's better to normalize this as far as it can go, but I don't see it practically. Location would then be only two fields- Name and ID. If that is so, then it would be logical to simply make the Address into Location and change it's place in the hierarchy (by rearranging relationships).

Which brings up another question... if I change the name of a table will it be changed throughout the solution or am I just asking for trouble? Would it be better to duplicate, rename and manually change everything?

I think this may be the revelation I was looking for- seems to fit. Still interested in suggestions on interface if you have ideas on that. Thanks!

[dansmith65]

FileMaker is great when it comes to re-naming elements (your table). It will properly update all the references to the table EXCEPT in places where you used indirection (if any). That is, if you referenced a field's name via a quoted string of text in an Evaluate() function; that reference will not be updated. The evaluate function is just an example; there are other functions/plug-ins that could reference elements by text.

What is your question about the interface? It seems too vague to give much of an answer.