I have a linking table (operators_equipmenttypes) associating one table, operators, with another table, equipmenttypes. Zero, one, or many operators can own a given equipment type. An operator can own one or many equipmenttypes... or more than one instance of a given equipment type.

These combinations of operators_equipmenttypes constitute work systems. The catch is that for different types of jobs an operator will use different combinations of equipment. My linking table has no way to differentiate between different work systems used on different jobs. So, I need a way to create an identifier (primary key) for each work system (which will include an operator and whatever equipment combination). I also need to associate two non-key attributes with each work system -- slope level and stocking level. These are important attributes I'll need to search against later. So, each work system (table is "worksystems"?) can have one operator and several pieces of equipment and will specialize in situations of mild, medium to steep slope and medium to dense stocking.

The confusion for me is that each record in the linking table can refer to only one of many possible operator/equipmenttype combinations in a given work system.

How do I get all this data into one record that can be uniquely identified as a work system?

Many thanks for anyone's thoughts, and kind regards.

I have a linking table (operators_equipmenttypes) associating one table, operators, with another table, equipmenttypes. Zero, one, or many operators can own a given equipment type. An operator can own one or many equipmenttypes... or more than one instance of a given equipment type.

These combinations of operators_equipmenttypes constitute work systems. The catch is that for different types of jobs an operator will use different combinations of equipment. My linking table has no way to differentiate between different work systems used on different jobs. So, I need a way to create an identifier (primary key) for each work system (which will include an operator and whatever equipment combination). I also need to associate two non-key attributes with each work system -- slope level and stocking level. These are important attributes I'll need to search against later. So, each work system (table is "worksystems"?) can have one operator and several pieces of equipment and will specialize in situations of mild, medium to steep slope and medium to dense stocking.

The confusion for me is that each record in the linking table can refer to only one of many possible operator/equipmenttype combinations in a given work system.

How do I get all this data into one record that can be uniquely identified as a work system?

Many thanks for anyone's thoughts, and kind regards.

EDIT: I think I get it. Can anyone say yea or nay? Create a table named "worksystems" with primary-key field "worksystemID" and non-key fields, slope and stocking. Then name my linking table "operators_equipmenttypes_worksystems," with operatorID, equipmenttypeID, and worksystemID as foreign, identifying keys (composite primary). Then put the whole system together as a view. Is this right?

EDIT EDIT: I do not know how this got posted as a new thread. My apologies, as I thought I was in edit mode.

Threads merged

pootle flump ur codings are working excelent.

why does it all have to be in one record?

you seem to have a deep understanding of the relationships in your data

we don't

and it's pretty hard to grasp what you're talking about without that understanding

thus, it's your job to paint the picture for us

I realized that, strictly speaking, it doesn't have to be in one record. With that said, however, it would be nice to have a set of uniquely identified off-the-shelf "operators_equipmenttypes_worksystems" combinations. Then, I could call an o_e_w combo into a table and have it associated (via table relationships) to the various attributes in the parent tables.

In this way, I wouldn't have to rely on a view to call the data associated with any given o_e_w combo. (I'm assuming that storing and running a query/view for such purposes incurs costs.)

I suppose I could accomplish this sort of task with a "library" of views, if that makes sense. Then, I could run a query/view, drawing the data from the various records (in the o_e_w table) that included my flag (like my worksystemID key set to a certain value). With this selected data, I could calculate whatever I needed to calculate and compare the results with values in a table or whatever I needed to do.

I wonder what's optimal from a data-storage point of view. That's the type of question, though, that I'll have to sort-of grow into. I think that once I code all this in and start messing around with it, I'll get more familiar with the role of views vis-a-vis physical tables, and this will all make more sense to me. I'm realizing the power of query is immense. (Rudy did suggest this direct approach a couple weeks ago...)

Re. the below, yes, I understand. As the model gets more involved, explaining the problematic/question gets more abstract and difficult... to the point that a good explanation can take up lots of the reader's time. Hence, I should use more ERDs and such devices. My apologies.

Re. the data, I spent the last two weeks conducting interviews with operators... trying to get to the specific mechanics of an operation so I could develop my business rules. So, doc, I know which parts hurt, but I don't (yet) know why.

In any case, these are the last little issues I need to resolve for the present. My conceptual model is more-or-less complete (for better or worse), so I guess it's time to start coding it into physical form.

Time to dive into Rudy's book (though some of these design books do offer some nice code templates to help us poor old n00bs along).