Question regarding duplicate table management

[hillborne]

Hello all,

I have an existing database that I need to add some functionality to. Specifically, I'd like the user to be able to ask a question or assign a task to any record in any table. So, for instance, they could ask a question about a particular quote or assign a task to a file saying 'please re-send,' etc... I'm basically creating a task management system.

A simplified entity-relationship diagram is attached.

On the right hand side of the diagram you can see the group of tables that I think I want to use.

A project is just a series of tasks, questions, notes assigned to particular people that sometimes results in the creation of a product.

The problem is that I have no idea how to do this correctly. My first instinct was to have the task/question/note table attached to the product table but there will be times that I'd like to ask a question about some files or finishes that aren't connected to a product and may never be connected.

So then I was thinking I'd duplicate the task,question,note table a dozen times and have it connected to each of the tables but I'd like one layout to manage all the tasks,questions,notes for a particular project and you can't have 1 layout based on 12 different tables.

My skills regarding FMP are simple but I was hoping someone has some advice or recommendations to get me started.

[Fenton]

I'd like the user to be able to ask a question or assign a task to any record in any table.

What you actually want to do is to assign a "record of any table" to a Task/Question, not the other way around. Otherwise a "record of any table" could only have 1 Task assigned. It is not just semantics. What that means structurally is that the ID of "any table" would need to be in the Task table, as a foreign key.

There's two ways I can think of to do this. You could either have an ID field from each of the other relevant tables in the Task table. Or you could put them all into one field, but use different prefixes or tags to identify which is which. Personally I use separate ID fields. I usually do this with a Notes table, and there's only a few other tables which would use it.

Wherever you create the Task/Question from would determine which ID field would be filled at first. You could then have drop-downs, or some other method to choose additional IDs, if desired. Likely this would be done in a portal, either with [x] Allow creation of related records, or scripted (with a sorted descending portal, most recent at top).

One question though. Can a Task be about more than 1 Product (or any other entity)? Because if it can, then you need to do all this joining in, well, a join table, between the Task table and the other relevant entity table.

[hillborne]

Hi Fenton,

I'm very grateful for your reply.

I understand what you are saying by having the Task_Question table hold the foreign key from the other tables.

So the user could navigate to the Quote table and use a portal to create a record in the Task_Question table, setting the primary key from Quote as the foreign key in Task_Question. I could then have a single layout based on Task_Question so I could review and track all tasks and questions....this is what I want.

However, isn't it true that you shouldn't have a continuous path between multiple tables such that a circle would form?

Because every table is potentially connected to a Product, I'm not sure how to also connect multiple tables to Task_Question without forming a loop. For example, since Quote and Files are already connected to Product, how can I also connect them to Task_Question without 'crossing the streams.'

To answer your question regarding Tasks covering multiple Products (or other entities)...The answer is yes, but having repetitious smaller tasks is actually more preferable in this situation than combining things into one task because it will make tracking of time and responsibilities easier. I'll be tracking the time a task is accepted to the time it is complete so the more granular the better.

It sounds like you handled something similar to this with a Notes table before so I'm very eager to hear how you did that.

Again, thank you very much for your time. I'm a furniture designer by trade so I'll gladly take any advice offered.

Edited December 11, 2007 by Guest

[Fenton]

However, isn't it true that you shouldn't have a continuous path between multiple tables such that a circle would form?

Well, FileMaker won't let you form a circle, so yes. But this is one of the benefits of the "anchor-buoy" method of building your Relationship Graph. Each of the main "entities" ("Quotes," "Jobs," etc.) has their very own Table Occurrence Group (TOG).

A TOG is a group of table occurrences (TO's), which are connected somehow. A separate TOG is therefore NOT connected. Each main Table (real table) has its own TOG. The other main tenet of the method is that the main data entry and navigational origin layout of each entity is assigned to the "anchor" of its table's TOG.

So, each main table would have its own connection to a different TO of the Tasks table. So there would be several different TOs of the Tasks table, each in a different TOG. Therefore no circle.

Tasks would also have its own TOG, with only 1 TO of Tasks (likely) as the "anchor." But with ties to new TOs of the other tables. If you wanted to "go to" Tasks, i.e., land on its main layout, you would "jump" to its TOG, during the Go To Related Record step (where you choose the layout to land on). Then, using all the TOs attached to it, you would jump back to wherever you wanted.

This is a critically important feature of FileMaker, which we use all the time, but take for granted. When using Go To Related Record, you can specify a layout of your target table which is NOT relationally connected. FileMaker dutifully puts you on the correct record of that unconnected layout. If you've checked "Show only related records," FileMaker transfers the related found set (according to the relationship specified) to the unconnected layout. (Notice "unconnected" -)

(P.S. This ability can be exploited to capture a found set, then restore it.)

[hillborne]

Oh, buoy...wow!

What's that phrase? Be careful what you ask for...you just might get it?

I will dutifully read, and re-read your post. Then I will go study up on this anchor-buoy method. It sounds like this is the key to it all.

This is a new level of database design that I wasn't aware of but due to my newness, there's probably a lot more!

Thank you very much for giving me a new direction, Fenton.

If I have further questions I'll make a new post about the specifics.

This is such a great forum!