Table Help

[dmontano]

I have attached an Excel spreadsheet that should clearly show the type of information I want to have records of.

I am trying to figure out if it should be one large "equipment" table, or if I should create separate tables for the different types of equipment since the fields are so different from one type of equipment to the next.

I have been looking for an answer all over the internet for over 3 weeks and can not get a definitive answer.

I will use this equipment record data to assign a vendor that has that type of equipment. More than one vendor may have the same equipment record.

Can anybody help me with this? My biggest problem is that many of the field labels for one equipment type are not applicable to another type of equipment.

Is it advisable to build a table as I have shown in the Exel file, or shoud I create 1 table for each of the different types?

Thanks in advance.

[bcooney]

Don't see the Excel file.

Edited April 13, 2008 by Guest

[dmontano]

I forgot to hit the add button after I "browsed" for it. Sorry about that. Hopefully it is attached now.

Thanks

equip_orgnztn_0804l_xls.zip

[dmontano]

Excel file is now attached. Any ideas?

[David Jondreau]

I am trying to figure out if it should be one large "equipment" table, or if I should create separate tables for the different types of equipment since the fields are so different from one type of equipment to the next.

I vote both. Create a main equipment table with all the universally shared fields. Include a field for Type. Then create subtables for all the types of equipment.

I will use this equipment record data to assign a vendor that has that type of equipment. More than one vendor may have the same equipment record.

Then you need a join table between your super-type Equipment table and the Vendor table.

[dmontano]

Thanks DJ,

Appreciate your vote. I do have a join table - still trying to build it out.

If I were to do as you advise, are there any disadvantages to doing so, versus 1 table?

[Fenton]

I looked a bit at the file. It appears to me to be one of those "6 of one, half dozen of the other" situations. They share enough fields to make separation questionable, yet not enough to make one table the clear choice. The fact that you say "beginner" also makes it difficult, as separation, even if it is the best structure, requires a bit more work up front (though it may make some things easier down the road).

I just wanted to chime in with an alternate method, which is fairly new and not well known as yet. It was proposed, on the TechNet mailing list (FileMaker's proprietary mailing list), by David Graham. So I'm dubbing it the "Graham separation" method.

In this method you have a table for each of the different "entities", with a central "anchor" table they all share. There is a 1-to-1 relationship between each outer table record and its inner table record. Each of the outer tables has their own auto-enter serial ID AND an anchor table ID (as a foreign key). The inner table has its own auto-enter serial ID. It also has a field which identifies which outer table that ID is being shared with. So, if you have 2 outer tables, with 6 records each, the inner table would have 12 records.

The important technique, discovered by David, is an exploitation of an ability of the [x] Allow creation of related records option, in a relationship. The relationship from a outer table to the inner table has this on. Records are always created in an outer table, for a particular "type" of entity. The relationship to the inner table is based on the local inner table foreign key, and the "type" (or an ID).

You enter data into some other related field of the inner table (on the outer table's layout) in order to cause the inner table record to be created. But (you object :-), there is nothing in that foreign key! Yes, that's the trick. FileMaker creates the inner table record, gets its serial ID, and passes it BACK to the outer table, populating the foreign key, so the relationship is joined. You really have to see it to believe it.

The upshot of this is that you have transparent creation of a joined inner table record for each outer table record.

Each outer "type" has its own table. Therefore a user looking at a list view or doing a Find in the outer table will see results of only that type. It is not necessary for them to separate the various types.

Conversely, if they want to see a list of all types, the operation can happen in the central table. For example, to show all different things in one portal.

Of course one could do that also if there was only 1 table. I think the crux of the choice as to whether this "hub" method is useful is the answer to these questions:

Do users almost always work with the objects as if they were different things, and expect to do Finds and see lists of only one type at a time?

Are so many of the fields different that it makes sense to have different tables?

Do they also sometimes need to work with them as if they were in the same table?

If all of the above are true, it's a good structure. But it was not clear from your Excel file whether the "enough different fields" was true. Only you can answer, "Do users also work with each type separately?" and, "Are they sometimes viewed as one?"

[bcooney]

I do not wish to hijack this thread, but I've looked at the David Graham example, and it is cool. However, I am so disciplined to use Anchor-Buoy that rethinking all context might just be the death of me. Is there a big enough payoff? Can you do the "DG method" and still AB?

[Raybaudi]

I just wanted to chime in with an alternate method... the "Graham separation" method.

Hi Fenton

can you post an example of this method ?

... or a link ?

[Fenton]

I don't think his method and a more traditional anchor-buoy are mutually exclusive. You could let each of the "outer" entities have their own anchor and TOG as well, with their main layouts attached to the anchor. They would then need a TO of the "common" table next to the anchor, as you would need to enter data in a related TO of the central table in order for the auto-entered ID to populate on both sides. In other words, use the traditional anchor-buoy layout attachments for data entry.

However, the reason you would use his method in the first place is that there would be somewhere where these "outer" tables needed to be seen as one. Otherwise there's no real reason to use the method, IMHO.

I haven't used the method enough to determine the advantages/disadvantages, optimal Relationship Graph structure.

But I can certain see its usefulness in certain situations. I think that company/contact/activities and medical/industrial databases are places where it could really help.

[Fenton]

This is a file that I posted earlier (which I can't seem to find here now). It was about selling Trucks and Trailers. Both are a "product" from a sales invoice point of view. But they share few fields, hence are better off in their own tables.

[P.S.] I take no credit for the "good" layout design, nor the "not-quite-so-good" design (in particular the BIG stuff :-)

CHN_Inventory_fej.fp7.zip

Edited April 20, 2008 by Guest

[Raybaudi]

You really have to see it to believe it.

Many thanks, Fenton, to let me see and believe it :

[bcooney]

Well, it's good to know that it's not an either or situation (Graham vs. A-:. Thanks for your opinion, Fenton, it's highly valued!

[Fenton]

I understand the logic behind his method, and can visualize situations where it would be a vast help. But I I'm still mostly thinking about when it would apply. I haven't had enough experience using it to discover the nuances.

The question you brought up is one I hadn't really thought about. Whether to attach the main layouts to separate TOGs of the tables (as usual A-:, or whether to attach them to the "common" TOG (which is how the example I saw did it).

The idea of the method is that you need to sometimes use the "two as one." However, that does not preclude using them entirely separately also. I think the TOGs and layouts would depend on how much "entirely separate" you had (separate child tables, etc.).

[dmontano]

I need to play catch-up with all the comments, as I have been away for a few days.

First off - thank you everyone for your contributions.

I even went out and bought a book NOT related to FileMaker as the scope of what I was wrestling with was not in "The Missing Manual". "The Missing Manual" - while a good book, did not offer the help I needed in this respect - so I bought "Database Design for Mere Mortals". Just started, hopefully that will help me.

Hi Fenton,

Your explanation / thought process is really helpful to someone of my limited abilities because I am searching for corrallaries I can understand so I can eventually say - "That's It!" In the book I just bought, there was mention of what you and DJ have tossed out in this thread for discussion - about a subtable (I think)

I must say, I have gotten different responses from other people and it has been quite confusing. Mostly because I have difficulty in explaining my scenario. I had been soomewhat convince to use one table - even though I suspected that I should have many tables. It just seemed fundamentally wrong to me to see so many fields that were blank and not contextually applicable to the "Equipment" type at hand.

I do not mind doing it the "best" way regardless of difficulty. I find myself spending weeks researching now instead of "building" because I have learned many of the things I had done in the past were wrong. So, I am motivated to do this the best way.

Using the "Graham method" you described, I will try to put it into literal context here to see if I understand:

In the attached ERD .jpg - the four bottom right tables I have are the equivalent to the "different entities", the far right blue table "equipment" is the equivalent to the "central anchor" table?

To answer your question at the bottom of Post #288333...

"Do users almost always work with the objects as if they were different things, and expect to do Finds and see lists of only one type at a time? YES

Are so many of the fields different that it makes sense to have different tables? YES

Do they also sometimes need to work with them as if they were in the same table?" YES

and furthermore,

"Do users also work with each type separately?" YES and, "Are they sometimes viewed as one?" YES

I would liek to add, that I would like to be able to see on my vendors layout (based on "vendors" table) all of the equipment related to one vendor. Therefore, the portal I have would need to be able to display that information. I believe, in the sample jpg I have posted, the portal would come from the "Vendor Equipment Association" table???

[Fenton]

You are well on your way. There is one trick you're missing. The relationship between the central Equipment table and its 1-to-1 tables is not based ONLY on their separate IDs (though you could also have them).* It is based on (or must include) the equipment_id.

The reason is that including it in the relationship is what triggers the automated "double-entry" of the id on both sides of the relationship, automatically creating the Equipment record AND passing its ID back to the original layout's table in one operation.** It is what makes the method transparent.

If you could post a simple version of your file I could add this. Or look carefully at the mechanism in the file I uploaded. This is a critical part of the method.

*There are essentially 2 methods for capturing the "type" of record in the central table. In David Graham's example file he just has a single field in the central table, with either a 1 or 0. In the originating table he has a constant field, of either 1 or 0. So that's how he does it.

An alternate method would be more like yours. Have the relationship based on the central ID, but also include the ID field of the originating table, targeting its own separate ID field in the central table. The "multiple ID fields" method. I suppose there's pros and cons to either method. His is more compact, less fields.

Another consideration would be if you were sorting the 1 field or multiple fields, say for a report in the central table. His method would sort by the single flag field. Yours would sort by multiple fields. His would be easier, but yours would be flexible (if you wanted to change the order of types).

I suppose you could use BOTH methods in the relationship. Since no one yet really knows which is better :-]

**This is accomplished by having at least one required field of the central table on the layout of the originating table. Entering data into the field creates the central record, via [x] Allow creation of related records in the relationship, on the central table side. The mechanism creates the record, which causes a new unique central table serial ID; which it then passes BACK to the originating table, to make the relationship valid (this is the whole trick :-). Think of it as a 1-record portal.

Edited April 21, 2008 by Guest

[David Jondreau]

I don't have much to add to Fenton's posts, except that I've been using Graham's approach in my recent CRM solutions and I like it.

I use the method Fenton details in his footnote, I don't use the primary key for the relationship between the main table and the subtable, just the pk from the main table. In fact, I don't use the pk field in the subtables at all, everything's based on the pk from the main table.

[dmontano]

Thank you both very much. I will have to read this again and again and again to unuderstand what is going on - or at least enough to formulate soome question to help me understand. Please pardon my ignorance. I am running quite short on experience and it is very easy to leap over my head.

I hope you both can pick this back up after I have had a crack at it and can to review my future post to help troubleshoot.

I would like to add, that I want to be able to build a "Hyperfinder" (if you will). An simple interface where I will be able to find a vendor or vendors who have a particular type of equipment. Does the "Graham Method" allow me to have the flexibility to build a search interface? Or would I be hobbled to some degree?

Thanks,

David

[Fenton]

The method allows everything normally allowed, and a couple more. The best thing would be for you to build a simplified version of your file, using the central table's ID as the main tie between the tables, as D J says. Then one of us could tweak the relationship as needed so that it worked. It is actually quite simple. It's just a little mind-boggling to think about :-]

[dmontano]

Much appreciated. I will start working on it and see how far I get by this afternoon.

Thanks!

[Fenton]

Basic ideas:

1. Common fields in common table.

2. Unique fields in outer tables.

3. Common table's unique ID in every one of above tables.

5. Relationships based on common ID and one other field (either to separate "type" ID fields, or to a single "Type" field).*

6. [x] Allow creation of related records, on the Common side of the relationships.

7. A required common table's field is enterable on the data entry layout of each of the outer tables, as a related field. (Actually there would likely be several common table's fields; but it's critical that data is entered in at least one of them, to create the common record, and get its ID.)

*This is an area where no one yet seems to know the optimal method. Though the "separate ID fields in the common table" seems a little messier, it may be that in your case, with multiple outer tables, it might give you more flexibility for Sort order of reports (in the Common table). On the other hand, you could always just use the related fields for sorts, or a custom sort based on value list for a single "Type" field. There wouldn't be that many types.

So, after all that, the optimal method is still unknown :-] Remember, even without any Type field or separate ID fields, you'd always know which table a common record is tied to, because the relationship would only be valid to one of the outer tables.

[dmontano]

While I wish I could say I have been absent due to a great extended vacation - I really have immersed myself on my lunch hour, evenings, and weekends, etc. to see if I could get up to speed and get my equipment table and the equipment subset tables functioning. I am flat-out stuck.

I believe I have set up the Equipment table and Equipment subset tables as prescribed by fenton, however, I am totally at a loss when it comes to connecting the relationships. I am sure I have totally compounded my problem when I try to build the layouts to create these types of records. Really at a loss on the layout to enter the raw data and then where to facilitate the join.

I have about a half dozen "things" I think of and am getting real confused by them.

For example, I have realized that the equipment in a subset table that I would like to associate with a particular vendor may be "configured" in numerous ways. If a particular "model" is to be associated with a vendor - that seems somewhat simple - however, that same model may be "configured" in a multitude of ways and then very unique to one particular vendor.

Since I have realized this, I am trying to figure out where and how to "customize" this "equipment" model from the subset further based on how the vendor has it configured.

If I have confused you - so am I. I am thinking that I have the customization or "configuration" options in the "Vendor Equipment" table that joins the "Equipment" to the "Vendor"???

So my tables would look like:

Vendor Companies

Vendor Equipment (Linking Table)

Equipment (Central Equipment Table)

Printing Equipment (Subset Table to Equipment)

Bindery Equipment (Subset Table to Equipment)

Any thoughts?

In the meantime, I will continue to stare off in space...

Thanks in advance.

[SurferNate]

Two cents (that's all it's worth from me), but:

It looks from your excel file that you have a large number of common similar values to each equipment type, values that don't seem to change so you might want a table to normalize just those values for each equipment type. Lets call that EquipmentTypes

Then you have each equipment item record, which has common fields, but differing values, that's your EquipmentItems table.

Then, following the example of DG structure, you have another separate Source Table for each EquipmentType (provided the EquipmentType values are all fully predetermined at time of database development). You can then add each piece of Equipment from its respective TO Layout, and have only the relevant fields on that layout.

I'm pretty sure that the only way this works is when, at time of development, you can conclusively state a manageable range of category or value types that the user cannot and will not change throughout the use of the solution.

[dmontano]

Thanks for the contributions all,

Hi Fenton,

As you know I have been working for months on this and am getting closer to my goal of merging two aspects together to solve my "Equipment" challenge.

You are aware of my categorical section I am working on trying to resolve (Star-join or Chain), coupled with the creation of a Conditional Value list for Categories, Subcategories, Type and Subtypes. While I have got that portion to work (using a star join), Comments reply is giving me reason to rethink if I should try and restruture the categorical section as a "chain" and then build the Conditioanl Value List. The primary purpose of that Conditional Value List is to select the appropriate values in an actual "Equipment Subset" table record upon creation.

That brings me back to this topic of the David Graham method... I am in the process of merging those two sections together and have ran into two distinct issues. I hope you can comment on what might be the snag.

Issue #1:

Using the David Graham method to related an Equipment Subset record to the Equipment Main table - I have noticed that if creating a record in the Equipment Table - no record is created in the Subset table or "Outer" table. Is this the only way it will work? Record creation originates from outer table only? If so, I just need to know so I can specify the field in the Equipment Main or "Inner" table to Find only in order to prevent record creation from that layout.

Issue #2:

My Conditional Value List works fine on the Equipment "Main" or "Central" TO as I would like: Select from the first drop down and right after the selection is made, the value shows up - before going into the second conditional value list menu.

However, when I apply the conditional value list to the Subset layout on the fields that originate from the Equipment "Central" TO, the values do not show up until I click somewhere outside of the fields. They work - but after the selection they are blank - once again, if I select somewhere else on the layout - the values show up.

I am guessing the record is committing when I select outside the field, then the value appears. However I do not have to do this on the "Equipment" TO layout. Do I have to replicate the Conditional Value List Structure and apply it to each of the 6 "Subset" or "Outer" tables in order to get the values to show up promptly?

This is driving me nuts! Any help is appreciated. Thanks.

[Fenton]

I think either method we've been discussing (loosely) would work. One may require more table occurrences than the other. I would be more inclined to the "chain" method myself.

Issue#1: The David Graham method should work both ways, but only if the fields in the relationship are stored on both sides; an unstored field on the target side will stop [x] Allow creation. You would need to put a field from the target table on the layout to create in that direction.

Issue2: Not sure. But something's off. Since you mention that it works on the layout of one TO, but not on one of another, it's likely a context problem. Look at the relationship from that location. Are the conditional relationships still valid? Filters using stored fields would have to be attached to the context TO, so likely will only work from one TO.

That's one reason why using global fields for the beginning and intermediate choices, only storing the final choice (as comment mentioned) is a more flexible method. That way your value list TOs can be in their own disconnected TOG, but work from wherever else.

That assumes the level of the "final" choice is consistent. But you said yours was, that each piece of equipment gets all 4 levels. Right?

[dmontano]

Thanks Fenton - off to scratch my head on getting this to work.

[dmontano]

Hi Fenton,

I have been re-reading my topics and you siad something at the bottom of your most recent post that has me thinking...

I do want to take this conditioal value list "with me" so to speak elsewhere. You mentioned globals and a comment made by Comment. It must have went right over my head and I missed it.

You are a little familiar with what I am trying to do - should I be building this value list using globals instead? Given that I need to categorize "Equipment" subset table records that are really using the same shared 4 fields for this categorization from the "Equipment" table?

Now I now someone out there will agree with me that this is confusing! Thanks again.

[Søren Dyhr]

The important technique, discovered by David, is an exploitation of an ability of the [x] Allow creation of related records option, in a relationship. The relationship from a outer table to the inner table has this on. Records are always created in an outer table, for a particular "type" of entity. The relationship to the inner table is based on the local inner table foreign key, and the "type" (or an ID).

This is a little confusing, and wouldn't have to justify a membership of technet solely on this topic - But isn't it the same as this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000128

...or what Bruce does with his global here:

http://www.fmforums.com/forum/showpost.php?post/149069/

--sd

[Fenton]

They are certainly similar, but not exactly the same. In the first example file(s), the data is typed from a "central" table into a child table. In David's method the common data (names, etc.) is typed from a child table into central table.

That is its main feature, that the common data from more than one child table is stored in a central table; yet also storing unique data for the child in its own table.

Bruce's example is more similar. You see the same undocumented behavior of "backwards population of a key when using [x] Allow creation," which is the trick of the method. But the implementation is different, for a different purpose.

Each is appropriate for its situation. David's is a method to allow two entities, say Companies and People, to function either as one entity (using the central table) or separate entities (the child tables) as required.

[P.S. I suppose the backwards population is not really required. It seems to me you could just use the regular IDs of the child tables into a central table, using prefixes on each ID field to prevent false matches. I've done that with a Notes type table (once, now I use separate ID fields instead). But the idea of a critical central table without its own auto-enter serial ID makes me nervous.]

[dmontano]

Hi Soren and Fenton,

I have been trying to apply the technique Fenton exposed me to a few months ago by David Graham in another thread where I just posted a sample FileMaker file. Hopefully I have been able to use the technique along with a data model approach that I am trying to implement (Parties data model).

Link is: http://fmforums.com/forum/showtopic.php?tid/198682/fromsearch/$fromactivity/tp/4/

Post# is: 315188

I was able to get the automated record creation without scripting it - which is something I wanted to do. I liked the idea of having the structure support the creation and deletion versus having to script it. I do not yet understand the pros and cons of one method over the other - I think I am still trying to grasp how to "implement" the "data models" I find appropriate for my solution.

The sample file has a ficticious "subtype" table called "animals" that would NEVER be used in the context presented - but I did that to clearly see what I was creating or deleting.

Any input is appreciated - thanks.