Relationships Graph Posted - In Need of Review

[dmontano]

I have attached a screenshot of the Relationships Graph for a portion of my solution that I have been wrestling with for months.

I have had many questions pop up while trying to build this and the straight answer seems to elude me - I am trying to learn. My questions ranged from:

1. Can you relate fk to fk

2. How many TOs away can you "show" related data in a portal

3. How to deal with what I thought / think is a many-to-many-to-many scenario

4. And I am sure a slew of other questions.

I am hoping that I can get some feedback from what I have posted here to either reinforce my line of thinking or to (once again) have input that shatters my thinking up to this point.

Goal:

Categorize Equipment into 4 discrete levels: Category, Subcategory, Type, and Subtype. I want to be able to create "Equipment Records" using this scheme right as a new equipment record is created. User will be prompted to enter Category first, Subcategory second and so forth.

Strategy:

Create a conditional value list that I can apply to any TO in my solution within the same file AND also have the basic TOs ready to build a conditional value list in other External files. Or at least the "recipe" on how to do so - based off of the "Equipment Categorization" star-join TO.

Problems:

Took forever to get to what I have now. I suspect I have built something that might not be the best way, or is fragile, or is just not the right way. How to build a FIND conditional value list.

Any feedback is greatly appreciated. If someone could take 5 minutes and delve into the details of the table set-up, relationship joins, the boat-load of TOs that I concocted to arrive at where I am - I would be grateful.

Thanks

[comment]

If someone could take 5 minutes and delve into the details of the table set-up, relationship joins, the boat-load of TOs that I concocted

I suspect that would take more than 5 minutes. I'd suggest you take a look at the file posted here:

http://www.fmforums.com/forum/showtopic.php?tid/174320/post/195641/#195641

and perhaps the one here:

http://www.fmforums.com/forum/showtopic.php?tid/190747/post/269504/#269504

One thing to bear in mind when dealing with a conditional value list is that (in most cases) it's only the last choice that really matters: for example, if you have selected Asia > China > Beijing, then given the city one can easily determine the country and the continent too, and there's no need to store them in the same record alongside the selected city.

[dmontano]

Thanks Comment for your reply and the links. I will investigate those. My noviceness obviously has reared its head in assuming that what I posted could be reviewed in 5 minutes.

Is this a reasonable question then with the graph I posted?

I am curious if the way I set up four tables (1 for each: Category, Subcategory, Type, and Subtype) and have those values join in the 5th table (Equipment Categorization) is appropriate?

I know there are other ways:

A. 1 table for all four values

B. 1 table for each of the 4 values but relate the tables together such as:

Category to Subcategory

Subcategory to Type

Type to Subtype

Then, the method I am "trying" where I have 4 separate unrelated tables and then I related them in the 5th table "Equipment Categorization".

I am not trying to cause more work - I much prefer simple and flexible - but rather trying to follow "Table Normalization" rules I have read about, trying to structure the data in as sound a method as possible, etc.

I have seen all sorts of ways to assocaite records and create conditional vaue lists - but my inexperience in being able to understand what the ramifications are of employing one method over another has caused my to "try" and do it by general database rules I have been reading. Furthermore, reverse engineering some of the examples I have seen can cause me to stare for a long time.

I may have been misinterpretting much of what I am reading in book form, but my attempt to follow along conservative and sound construction is my goal.

[comment]

The various category levels can be in a single table or in separate tables. There is no single correct answer to this. A single table is simpler, but it requires redundant entries for the higher level categories.

One thing I am fairly certain of: if the tables are separate, they should be related. The relationships should be daisy chained as:

Equipment >- Subtype >- Type >- Subcategory >- Category

This way, selecting the subtype automatically assigns the equipment to all the correct higher-level categories. A direct relationship between Equipment and e.g. Category is possible, but only as an auxiliary device to help the selection process. Otherwise it would be easy to set up contradicting selections, e.g. a person lives in Beijing, in the country of France, in the continent of Africa.

[Fenton]

It is an interesting question. One of the reasons why it's difficult to say which would be "best" is that a lot depends on how/when you edit the entries, and how you use them.

The "one-table" method works well (enough) when the entries are fairly static, with a fixed number of levels, always assigned. Like geographical data, etc..

But if the data is going to require editing, by the user, then the multiple tables is I think better. Because you can pop up a window of the table, and the usual things, record count, etc., will work.

If the number of levels assigned is not fixed, then I think multiple tables are required. By that I mean if you only assign say a Category, and Subcategory, but either may not, or are not ready to, assign a Type and SubType.

It is also less maintenance if you decide the edit (slightly) the names of the values later. You can have the values only in their reference table, and use their IDs everywhere else, which are auto-enter fields in their reference table.

[comment]

I agree - those are all valid considerations, and there may be others as well. Separate tables are certainly required when the "categories" are tracked entities on their own, e.g.

Occupant >- Apartment >- Building

If the number of levels assigned is not fixed, then I think multiple tables are required. By that I mean if you only assign say a Category, and Subcategory, but either may not, or are not ready to, assign a Type and SubType.

Interesting point. Another possible solution here may be a single table with recursive hierarchy. I believe that would be most suitable for assigning employees to various units within the corporate structure, for example. So you can belong to a division by virtue of being assigned to one of the division's departments, or you can belong to a division directly by working at the division's HQ - both handled the same way, with a single assignment to the lowest applicable level.

[dmontano]

Hi Fenton! and thanks Comment for continuing this dialog - I truly need more help than the four books I currently have. I have wrestled with this for months - no exaggeration.

I do have a "linking" or "join" table (called Equipment Categorization) where I will be assigning the 4 levels together - so to speak - to create the unique combinations. I have an additional field in the join table called "validator" that auto-enters each of the 4 fields I have selected via drop down and then ask to validate for uniqueness.

So far - it looks like it is doing the job. However, I am looking for insight into my methodology, if I am going about this incorrectly - or more importantly, creating a data problem.

I have done this because I realized (or think) that I have to build the raw values first and then relate them afterwards in any event.

If I choose to relate Category to Subcategory; then Subcategory to Type; then Type to Subtype I can do the assignment right as I enter a new value - or I can do it once with all four categoircal levels in the join table. Since I have a one-to-many-to-many-to-many - I think this "star join" resolves all of these relationships. Otherwise, I think I would need a join table between Subcategories and Types, Types and Subtypes. And the thought of me trying to figure out how to create a conditioanl value list out of that would cause me to give up.

So, I have chosen to have one layout (based on this join table called "Equipment Categorization" where I select from the "Custom Value" drop downs (4 of them - 1 for each "level") in order to assign the association.

This "Equipment Categorization" will in turn be the Table that I use to base all conditional value lists where this organization structure is required - for example, in my "Equipment" table.

I will not trouble this conversation with that as I have posted that topic a couple of months ago and before I can get back to building that portion - I felt like I had to get this one figured out first.

Comment, the conditional value list links you provided were very helpful. My dilimma with trying to build them is within the structure that I have presented above. Specifically that the raw values are in 4 tables. Bringing them into 1 table (the star-join) gives me a chance to get my head around the connections.

I did finally get it to work and hopefully after this portion is hammered out - I can try to illustrate what I had to do to get a conditional value list out of our current topic.

Thanks for any more feedback,

David

[comment]

I am afraid you've lost me. I don't see the need for a join table or a star join here. As you say, the relationships are one-to-many-to-many. A record at any level needs to know only who is its parent. The grandparent is known implicitly, because the parent knows ITS parent, and so on.

[dmontano]

Hi Comment,

I have actually confused myself as well. Sorry - I am trying to be clear, but the confusion is originating with me in this respect:

Originally I thought I had a straight forward relationship with:

Category --< Subcategory

Subcategory --< Type

Type --< Subtype

Then I started thinking "What if I had values that were many-to-many-to-many?", such as:

Color>------

If a shirt can be ordered in any of 5 colors, in any of 5 sizes, and in any of 5 materials... that would be a different scenario to build tables and how they are related?

I believe the "shirt" example would require 2 join tables: one inbetween Color and Sizes (color_sizes) and one inbetween Sizes and Materials (size_materals). I am not sure.

(Then the trouble began - I started thinking)

I had read this sort of many-to-many-to-many could be resolved with one join table that brings them all together at once.

Since I could not figure out if I had one type of relationship arrangement over the other - I started wondering if even though I think it is the type of relationship you described - what if it changes into the other because of the values. Sorry if this is blatanly clear to others - to me it is not.

In desparation - I thought I will build it with one join table to relate all values as I can not see where it would hurt or compromise the data - whether it was a one-to-many, or a many-to-many AND that would spare me the trouble if the values changed in a way that the relationship changed.

I am starting to think I have lost my marbles.

Is it possible to build for a many-to-many-to-many and have the structure work either way?

Is this a fundamental mistake?

What you have stated makes sense. That is what I thought to begin with. In short, I am obviously having a hard time in recognizing the different types of relations.

Be easy with the meager... any help appreciated.

[Fenton]

Hi David. I didn't recognize that it was you at first, though I thought, "This looks a lot like that complex value list thing that David had" (duh).

I think I see the basic idea of what you're doing, a kind of combination of the "multiple tables method," as far as defining the values, and getting an ID for each; then using another table, as you would for the "one table" method, where you create the records with all 4 values in each record. But you're using IDs, instead of names. This is a good method I suppose, though I believe you could get much the same effect by just use the relational "chain" as comment outlined.

Yours has one advantage, which is that you can easily count the unique combos of all 4, though I don't see that as very useful info. But I think the "chain" method would use less TOs.

[Fenton]

I begin to see further differences between the "chain" and the "one table with unique combo of all" approaches. There is conceptual difference between them. The "chain" is more of general "logical" system, whereas the "unique combo" systems would be reflected a more fixed reality of objects.

Your "clothes" example shows a "objects" reality. You could have any combination of different types of clothes, some types only available in certain sizes AND colors. There need be very little logic to it, it's just whatever you decided to carry. It would require the one table of unique combos.

Whereas if the associations are more logical, then the chain method makes sense.

[dmontano]

Hi Fenton,

Well initially I started with one table. And used that as the basis TO for a Conditional Value List. Then I was exposed to creating tables to avoid duplicate values in different records.

My one table approach suffered from that. So, I thought, I really want to do this the right way so down the road when I am making value edits it will be with a high degree of data integrity. So, I bought into the 4 separate tables approach.

Then, that led me to another problem: how to build a conditional value list when the values are not in one table? but rather spread out over four tables.

So, I thought, since I still need to associate a Subcategory to a Category (for example) in the Subcategory table using the chain method, why not do all of the associating back into one table that links them all there?

That would give me an overview of all the associations in one table that I can then make a conditional value list with elsewhere in my solution.

Then that turned out to be what you see now - a lot of TOs that I am not experienced with. It appears to be working - but I have no idea if I have built a Frankenstein or not.

This type of categorization / organization is going to be critical for me to repeat in other areas and so I was using this part of my solution as the springboard to burn it into my brain on how to go about doing it.

Both your help and Comments are appreciated.

[comment]

If a shirt can be ordered in any of 5 colors, in any of 5 sizes, and in any of 5 materials... that would be a different scenario to build tables and how they are related?

Yes, I think it would be very different. Because the choice of color does not depend on the chosen size ot the material. Unless you want it to, but then you'd probably want to establish some hierarchical order to the choices, and also set the order of selection.

Now, it would be possible to have a star join table, listing all the possible combinations of color/size/material for a specific shirt. It would also be possible (I think) to make the selection process flexible, so that if you selected the size first, you'd get to choose only from colors that are available with the selected size, and so on. But it wouldn't be simple.

Roughly, you'd need a calculation field for each selection field, saying 'If no choice has been made, then all.' The relationship would be based on all three calculation fields*, so it would be filtered by any choice made - and so would be the three value lists based on this single relationship.

---

(*) and the ShirtID, of course.

Edited July 3, 2008 by Guest

[dmontano]

Thank you for your contributions...

But can I ask for a little clarification? Let's discard the shirt scenario - Comments answer regarding the shirt versus categories indicate they are indeed different and should be treated differently.

Back to categories, subcategories, etc.

Will the star join work for categories? If not, why? I am not arguing, rather trying to see the logic in why it would not work.

If it would work without data integrity problems - are there more TOs associated with trying to create a conditioanl value list from the "star join" version the "chain" method.

Thanks

[comment]

I don't think a star join is suitable here, because it doesn't represent the true relationships between entities. In a hierarchical structure like this, each equipment belongs to a subtype, and to a subtype only. There is no direct relationship between Equipment and Category. In fact, you could move a subtype to a different category, and all the associated equipment would move along with it.

[dmontano]

Okay, I have just chained the relationships and just ran into a snag...

4 Level are as such:

Category

Subcategory

Types

Subtypes

When associating the "types" to "subcategories" I have a few instances where a "type" needs to be assigned to more than one "Subcategory".

Forgive me if this is obvious but that is a many-to-many - right?

If so, I will have the same thing occuring with "subtypes" to "types" for sure. So that is another many-to-many.

So if I have 4 tables (1 for each level), do I now need to build join tables for each many-to-many? If so, I have a feeling building a conditional value list will be a little overwhelming. That would be a total of 6 tables - maybe 7 if I have to create a join table between subcategory and category...

I can not seem to get this structure figured out. Any help - again - is appreciated.

[comment]

When associating the "types" to "subcategories" I have a few instances where a "type" needs to be assigned to more than one "Subcategory".

Forgive me if this is obvious but that is a many-to-many - right?

Maybe, maybe not. Probably not, but I can't really say without understanding the real entities behind the solution, and the purpose of this classification.

Let's take a simple example: I have two categories, Toys and Weapons. Toys can be either mechanical or electronic. Weapons too can be be either mechanical or electronic. Now I can look at it this way:

Toys:

• Mechanical

• Electronic

Weapons:

• Mechanical

• Electronic

Actually, a better way to express this would be:

Toys:

• Mechanical Toys

• Electronic Toys

Weapons:

• Mechanical Weapons

• Electronic Weapons

In this approach, there is nothing common between 'Mechanical Toys' and 'Mechanical Weapons' - all they share is a name, but for me that's merely a coincidence.

OTOH, I may look at it this way: Is it a toy, or is it a weapon? Is it mechanical, or is it electronic? If these two questions can be asked INDEPENDENTLY of each other, then you're NOT talking about hierarchical classification anymore. In fact, you're back to the case of the shirt with three independent classifications by color, size and material.

This is not really a question of data modeling, but of business logic. Or rather, it's a question of business logic before it can become a question of data modeling. You need to decide what's the best business model for you, then let the data model follow.

[dmontano]

That was well put Comment. Sincere apologies for the ignorance. I do not know if the following will help you in helping me to determine which structure for the 4 "levels" of organization for these values.

At first I believed my categorization was "hierarchical". Then when filling out the values I "happened" upon this instance:

Sample 1

Category: Printing

Subcategory: Printer

Type: Laser

Subtype: Sheetfed

Sample 2

Category: Printing

Subcategory: Copier

Type: Laser

Subtype: Sheetfed

As you can see, the term "laser" can be classified as a "Type" for both a "printer" and a "copier". So, in the chain method, I have each table set up to "not allow" duplicate names (my attempt at data integrity).

I think the whole idea behind "chaining" or "star-joining" anything - is to associate the items together. However, doesn't "Table Normalization" dictate that I should have one unique value in the context of how I am "using" these 4 raw value tables?

I could turn-off the validation for unique names and duplicate the associated record for "Laser to Printer", and "create a second record for "Copier to Laser" but then I am trying to create a environment that only allows changing one "value" and all instances reflect that change. As I go deeper to the "Type" to "Subtype" I have many more instances such as this.

I may be holding onto the things I have read and misinterpreting them at the same time. That is why I thought a "join" table that allows me to bring any 4 values in and validate in this new "join" table to only allow unique combinations would solve "my puzzle".

It would allow me to have only one value of each instance in their respective tables... then associate all 4 at once.:

I have been all over the map on this: I started with one table with all four values associated right from the start, then I was exposed to the "separate" entities ideology (which made sense) so I built 4 tables and chained them; then I ran into the above scenario; then I came across a "star-join" example in a book.

All this to build a conditional value list to fulfill the organization requirements I have when a new piece of equipment comes across the radar.

Lastly, I am really trying to learn because I will need the ability to apply organizational "tools" in many other areas of my solution.

[comment]

I'll try to address this later - meanwhile I suggest you read this:

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

(you might need to read a few of the earlier posts too to understand the context.)

[Fenton]

Here is an example file which uses the "chain" method. It also has a mechanism to clear the secondary fields if a previous choice is modified; which is modified version from an example file by comment, "condVL2+clear2.fp7"

The example file allows you to use the same "value name" more than once in one of the categorization tables. Each instance is a unique record, with its own ID. Since it has a different parent, and since its value list(s) are always filtered by the parent, it still works; i.e., it may look like the same value, but it isn't.

Let me know if something doesn't work, as I plan to use this myself :-]

(P.S. It does not "validate" the values. I suppose you could, but since you'd be entering them in a portal, which could be sorted by name, you would not enter a duplicate.)

[i edited the file slightly; removed the global fields.]

MultipleValueListsFilter.fp7.zip

[dmontano]

Hi Comment,

That was quite a thread to read and wrap my head around. I think the jest of what you are pointing out is there are many ways in which people can interpret data, its structure, its place in the universe, and its place in a solution.

Seems like there are no straight-forward answers to even something seemingly simple as phone numbers or addresses.

These topics can turn into debates where each person has a valid point of perspective. And to say one perspective is wrong - is perhaps saying one thinks that their perspective outweighs another. When in fact - given the context of what the solution is suppose to provide at the real world level - may be perfectly acceptable. One may be relational model correct while the other may be "solution" correct and both acceptable.

Unfortunately for me - while I am very interested - I do not have the depth of knowledge in this field to be able to "see" my path. I think I have enough "age" and common sense though to see that with enough "time" and commitment - these topics will start to become a little less muddied for me. Until then - you will see more posts originating from me.

I do not want to overcomplicate what I am trying to build. However, I am trying to build something that will not fall apart because I am not following the basic building blocks of database design.

Please keep in mind - I am NOT debating, rather asking questions in hopes of being enlightened. I have no leg to stand on in these matters and what may appear as me holding a position - is truly me asking to get a persons "perspective" as to why?

[dmontano]

Hi Fenton,

Thank you so much. I am going to dig into this file and see how it works. My first step will be to deconstruct and rebuild based on my "naming" conventions" otherwise I get SO confused.

I did pull the sample file Comment posted - but was looking at it from a different perspective.

I have not worked AT ALL with globals as you have in the example file. Maybe that was a missing component all along for me in my file.

If you can bear with me on this, I will try to rebuild your file based on my "simple-minded" perspective and put things in the context I am familiar with so I can ask further questions.

In short - I am askign you to be flexible in interpretting my data conventions - as I struggle with more advanced conventions.

I will have this reconfigured in a couple of hours.

[Fenton]

Actually, I did not work with the globals in the example file (which I reposted, without them). I found that in this case they were not very useful; I would have needed just as many TOs to use them, and you may as well get the other IDs anyway.

[dmontano]

Okay, I have started to dig in to the file posted and I am rereading the posted words. So as it is set up, any one of the "categorical" tables "could" have a duplicate name. I understand it will be a unique record with unique serial ID, but I guess this is where I came to a fork in the road:

I will have tables that have duplicate values. True?

If so, is that denormalizing a Table? True?

If it is denormalizing a table, is this acceptable because of the fact that the value is dependent upon its parent? In other words, in the context of how the value is chosen - it is unique in its "chain"?

Which leads me to this question...

I have to assume the value will be duplicated through use, and I should assume that the value will need to be edited, and that means I would have to search for all occurences of that value in how it was used in any possible combinations in order to complete the edit.

Which leads me to this question...

If I were to create each of the 4 tables where only unique names existed - that would ensure that I never have duplicate values. But, I have then have to associate or related them in a fifth table "via menu selections".

This is interesting to me because of what a few others have said make sense as well. The true structure of the relationship is "chained" - the values are dependent upon the one preceeding it. However, the association using that structure WOULD yield duplicate values - unless I use a join table of some sort.

I am not trying to beat a dead horse here - but I think this may go a long way in helping me understand "relations" and table construct. So while I am dragging this out - I might as well keep going...

It sounds like from the true relationship is:

One "Category" to many "Subcategories";

Many "Subcategories" to One "Subcategory_Type" join;

One "Subcategory_Type" join to Many "Types";

Many "Types" to One "Subcategory_Type" join;

One "Subtype-type" join to many "Subtypes"

If this is true, then the question becomes how many ways could this be "modeled" in Filemaker? And then, which one is suitable for the reason the tables were identified as entities in the first place.

If I chain with only 4 tables (Category Subcategory, Type, and Subtype) with no join tables, I will have duplicate values. If I model with a "star-join" I will not have duplicates in the source tables - but no representation of the relationship in the graph. The representation of the relationship is only established via the record in the star-join table.

Is there any downsides to creating this as a star join? We have went back and forth on this - mainly becasue I did not know the relations of these entities - perhaps I still do not.

Comments observations have made me believe that I do not have a true heirarchical relationship and therefore I am leaning back to a star join. My confusion must be clearly evident by now. If anyone unequivacally can see what I have here - please feel free to chime in.

[comment]

Seems like there are no straight-forward answers to even something seemingly simple as phone numbers or addresses.

I am not sure I would go that far. But there is a question here that was never answered: what is the purpose of all this?

To build a conditional value list is not a purpose. To classify equipment into categories is not a purpose. These are merely tools that can be used in order to achieve the REAL purpose - of which we know practically nothing.

The question you asked earlier about the printer and copier: only you (or your client) can answer that. The question - at this point - is NOT HOW to classify them, but WHY classify them. Will the fact that both are of type "Laser" ever be significant to the one that uses this solution? If so, how?

A database is not a military parade-ground: you don't organize information for the sake of organization. You organize it so it can be retrieved in a meaningful manner - and the definition of "meaningful" depends on the purpose. You simply have to put yourself in user's shoes and ask what kind of questions they will be asking that the database is supposed to answer for them. Who knows, perhaps you'll discover that all they really need is just a list of tagwords that an object is associated with?

[Fenton]

I would agree with comment, that it becomes difficult to talk about what is the "correct" relational structure for a given task, unless one knows practically everything about it. My general approach is the simplest sound structure that produces the result I want.

If it is denormalizing a table, is this acceptable because of the fact that the value is dependent upon its parent? In other words, in the context of how the value is chosen - it is unique in its "chain"?

Yes, it is true that my "chain" method will have duplicate values in its tables. But it will not have any duplicate primary serial IDs. In this case it seems to me that 99% of the records would not have duplicate values. Which makes a join table inappropriate. It could also be done with a unique value, but using a multi-line ParentID. That would work, but be awkward for data entry.

Since the data for the child records is always handled via filtered portals from the parent, the duplicate value becomes in many ways a non-issue. Yes, it if you decided to change the value later, you might have to change it in a couple or few records. But, it is just as likely that when you decided to change it, you decided to split the meaning. That is, you might change it to one value for one parent, but a slightly different value for a different parent. From the users point of view, this would just be handled independently for each in the portal from each parent.

In my example the 4 choices produce a combination that uniquely identifies the categorization. It is also true that each of the IDs will likely be needed for finds and summarization purposes (reports).

Your method, which I'll dub the "dmontano combination table" method, has separate tables for each entity, with both unique primary IDs AND unique values. The IDs are then entered into central table of predetermined combinations. This has a couple of features. The first is that it is possible to have a single unique serial ID that identifies the combination. In practice I don't see the real use for this, as you require the other IDs anyway, for summarization, etc..

The main pro or con (depending) of the method is that all "valid" combinations would need to be pre-entered. Of course, in the "chain" method, the values need to be pre-entered, but not the combinations. And also, in yours, a new combination could be added at any time, likely by choosing all 4 values into global fields, then creating a new record with them.

It would be easier to change a value later, as it would only exist in one place. You wouldn't have to approach from its parent. It would be more difficult to "split" it for different parents, for new meanings.

The combo table could be used to validate choices. But the chain method validates also, according to the hierarchy. The combo table could discourage a particular combination, which the chain method would allow, by simply leaving it out of the combo table.

So, the combo table is a workable solution if you know all (or most) of the valid combinations and are willing to pre-enter them. This would work OK for many solutions. But if there are a large number of combinations it would be an awful lot of extra work for the developer.

[dmontano]

Hi Comment,

Most people would think a Vendor Database is just a Contact Module for vendors - addresses, phone, email, etc. While this is one aspect of the solution, there are many processes that I am trying to faciliate. One of those that would benefit from a categorical structure relating to a Vendors Equipment is the RFQ or Request For Quote portion of the solution.

Selecting the "right" vendor can be interpretted by many people in many different contexts. Choose a Vendor that is closest in area, a Vendor that is least expensive, a Vendor of the right type, a Vendor that has the right equipment, etc.

Much of the above paragraph hinges on purchasing product or services from vendors. One goal of the solution is to cull the herd of Vendors, but do it in a way where many of those purchasing or specifying will not have to have a "great" deal of technical knowledge in order to specifying which vendors are "optimal" based on a given project. The organization of equipment helps cull the herd through this selection process.

I am looking at Equipment from the perspective that if an Approved Vendor has Equipment - it is almost an extension of our equipment capabilities and where do I want to produce the project.

For example, we may have 3 dozen printers, those printers may have anywhere from 3, 4, to 20 printing presses.

It is not uncommon at all - in fact very typical - for someone to submit a RFQ to a vendor that literally does not have the actual equipment. The vendor will "broker" that project out and bid on it. Typically elevating the cost. In other cases, a printer may be able to do the project with their equipment - however, it is fundamentally inefficient due to the configuration of their equipment.

Big deal - right? Well, in this example, the amount of money involved can be significant - enough to cover an average persons salary for a year - in some cases. That is the extreme cost-analysis. The additional benefit is it helps people make knowledgable selections without having the technical background to know that a project is better suited on a double web press versus a sheetfed press.

I hope that helps put a face on the purpose.

[comment]

I'm afraid I don't follow your explanation, but it doesn't really matter if I do - what's really important is that you understand it. I couldn't tell you what the "correct" solution is without spending anywhere between 3 days to 3 weeks at your location anyway. All we can do here is present you with some models for you to choose from (or adapt at will).

My hunch is that you cannot squeeze EVERYTHING into a single hierarchical model, and that you need a "multi-dimensional" classification system. For example, the type of equipment could be selected using a hierarchical category/subcategory/etc. mechanism. Other attributes, e.g. price, location, or whatever would be selected independently (using whatever mechanism is appropriate for them).

I don't see how a star join table would be useful here. You can very easily find a vendor in Indiana that sells any product under $500 in the Ink category, WITHOUT having a predefined record for 'vendors in Indiana that sell products under $500 in the Ink category'.

[Fenton]

It sounds like to me that you are trying to match "jobs" (which you need done) to equipment which Vendors have (among other things). As long as you use the same categorization method on both I don't see a big problem with that.

As far as price goes, that would likely be a Sort after you found matches.

[dmontano]

Fenton and Comment...

Many thanks for not abandoning the small-minded. I really appreciate it.

It is obvious there are more than a couple of layers that I am inexperienced with: the data model, the relationship model, the implementation of that into FileMaker to become a viable tool.

Because of this, both of you may feel we are going in circles on this topic. I assure you at least I am getting more from this than I realize. Hopefully one day these things will start to fall in place for me.

The star-join was something I read - I may have mistakenly latched onto the concept because I could see a corollary with what I "thought" I had. Hopefully time will help me see clearer.

Fenton, thanks for the sample you posted, I worked on tearing it apart to see if I could integrate it into my scheme and I got frustrated - stayed up till 2:00 this morning.

I think it best before I use up all goodwill that both of you have extended and let this dog lay for a while. Maybe I can try to build both to help me see why some things work and some don't.

My frustration enters the picture when I am trying to attach the "conditional" value list feature to the "Equipment" subset record creation. I did get this to work - as mentioned in an earlier thread - however, the values are not "immediately" appearing after the selection. They will appear, but I have to select somewhere outside of the menu in order for FileMaker to seemingly "refresh" to show the values selected. All related fields are indexed.

Oddly, when I attached the conditional value list to the "Central" equipment table - the menus do not exhibit this "refresh" issue. I am wondering if this "refresh" issue has something to do with the layout where I am using the list is of related values 1 TO away from Equipment, but conditioanl values are one TO away from Equipment.

Off to work on it. Thanks again.

[Fenton]

First, the globals won't work here, as you're not making global choices. I mentioned them, then I later "dementioned" them :-] I'm afraid you'll need the TOs to make each choice.

In the case of the "central" table, the TOs for the choices would be attached to the central table. Because the categorization fields are IN the central table. You can still put them on a layout in the "sub-table" and use them. Because the Value List says, "starting from" the central table.