Working with a list of items from a separate table

[wedgeman]

I'm having a bit of a brain fart, and I'm missing the obvious (or at least in my own mind) - can somebody throw me a lifeline?

This is a project management solution and I'm trying to relate checklists into the project.

In P1 table (main project), there is an area for checklists. each checklist may have 20-200 points for checking.. Checklists may include a simple yes/no check, data entry, multiple choice, etc.. (these main fields are the same across all checklists, which is why I categorized all checklists to a single table..

The value names for each line of these checklists (there are numerous checklists related by project type) are in a different table [PC1].

My plan was to hold checklists in the separate PC1 table, relate by project type and show in a portal, but i'm realizing this isn't going to work. When I pull that table into P1, i have to have the static field names (from PC1) show up, but the data for each of these items needs to stay with the project (P1). How should I build/arrange a relationship so that when the correct project type is chosen, the correct checklist is brought forward and the correct fields are available? 

The problem is that in say P1 Main view, if I choose ProjectTypexxx, I have to pull the static checklist from PC1-Recordxxx (relation by type), but i have to have related fields for each of the checkpoints in PC1Recordxxx..  in PC1Recordyyy, there may be a different number of checkpoints, so the list may be longer or shorter..

I'm a bit unsure if i need to add the data fields for each of these possible related checklist items into P1, or create a separate OTHER checklist (PC2), and store that data there, and populate it by a relationship based on ProjectType. Or is there a better way?

thx!

Edited June 2, 2015 by wedgeman

[Kris M]

Go Directly to Database Normalization. http://en.wikipedia.org/wiki/Database_normalization

Do not pass go. Do not collect $200.

 

[wedgeman]

Thanks.... I think.

yes, I *KNOW* the setup is not fully normalized.

I'm a bit lost as to how to further break it down.. a  join table to hold the fields? How would/should I then have the fields connect and view?

[eos]

IIUC, you could do it this way:

Create a new table [2]; use table [3] as a “blueprint of checks by project type”; for a new record in [1], create new records in [2] by “copying over” the appropriate checks; along the lines of

Project [1] --< Check [2] >-- CheckByType [3] { >-- ProjectType }

Your actual checklist is then the set of related records in [2], accurate as of the time of project creation.

[eos]

Go Directly to Database Normalization. http://en.wikipedia.org/wiki/Database_normalization
Do not pass go. Do not collect $200.

​Gee, that's witty.

[Kris M]

​Gee, that's witty.

​Hey someones got to be a PITA. Might as well be me.

[wedgeman]

IIUC, you could do it this way:

Create a new table [2]; use table [3] as a “blueprint of checks by project type”; for a new record in [1], create new records in [2] by “copying over” the appropriate checks; along the lines of

Project [1] --< Check [2] >-- CheckByType [3] { >-- ProjectType }

Your actual checklist is then the set of related records in [2], accurate as of the time of project creation.

​many thanks..

 

yes, I was afraid that would have to be the route...

I did something similar in another db, but thought perhaps there was/is a better way to pull it off.

And thanks for not being a PITA....

[eos]

​someones got to be a PITA.

Been there, done that (and yes, I did …). Have fun. 

[comment]

My plan was to hold checklists in the separate PC1 table, relate by project type and show in a portal, but i'm realizing this isn't going to work.

​If you mean that each item in a checklist would be a separate record, there's no reason why this couldn't work - but you will need separate tables for the items and their values, along the lines of:

In terms of practical implementation, it would be convenient to have an auxiliary relationship linking Attributes to a TO of AttributeValues based on matching:

Attributes::AttributeID = AttributeValues 2::AttributeID
AND
Attributes::gProjectID = AttributeValues 2::ProjectID

Then place a portal to Attributes (showing also the value field/s from AttributeValues 2) on a layout of Projects, and use a script trigger to set the Attributes::gProjectID field to the ID of the currently viewed project.

[Kris M]

Been there, done that (and yes, I did …). Have fun. 

​Aint havin fun not doing it