Best way to start a comparison list.

[rgEffects]

I have 36 parameters that must be assigned to a user and a document. The 36 parameters are unique and they will be either on or off. There will be no nulls in the list. Each user and each document will be assigned 1 or more of these parameters. When the user assignments match the document assignments the document will be displayed.

I can do this with 36 separate tables, one for each parameter and tie the user ID or the page ID to the table. For example:

primaryID, user ID, documentID, parameterOn/Off

This will leave a null in one column because a single record will either be assigned to a user or a document.

I could set up 72 tables like this:

for the document
primaryID, documentID, parameterOn/Off

for the user
primaryID, userID, parameterOn/Off

or I could set up a single table with as follows:

primaryID, userID, documentID, parameter1On/Off, parameter 2 .... and so on for all 36 parameters.

What do you think would be the best approach to simplify the task of matching document 1 with parameter 1, 5, and 9 to user 1 with parameter 1, user 2 with parameter 9, or user 3 with parameter 1 and 5 set to on?

[AndrewGSW]

It is hard for me to see this clearly, but do you really need to store 36 on/off values? Can you not just store the parameters (or an id number for them) that confirms which parameters are relevant?

My first impression is:

users (table)
____
userID, etc.

documents
____
documentID, etc.

userdocs
____
userID
documentID

documentparams
____
documentID
parameter (a number or id?)

userparams
____
userID
parameter

[rgEffects]

Do I really need to store 36 parameters? well a manager will have full access so managers will have a yes at all 36, however a QC specialist will may only have access to 4 parameters. Every document will have at least 1. Some will need to be seen by both a QC and a CFO but, but the CFO won't have access to all of the QC documents.

Sounds complex, and it is, but every user will have one set of parameters and every document will have another.

I can't be assigning docs to users, I've got to assign docs to parameters. We call them bizCats (business categories) so I need to match up the bizCat between the user and the document. As work progresses we need to be able to add a bizCat to a document or take it away so that it can be reviewed. For example if the QC department passes a document then the QC is removed and QA is added so the QC sees the document as completed and QA sees that it is their turn.

In your list are you thinking that DocumentParams will be all 36 columns? That was my first thought... If I just assign the appropriate bizCats to a document or a user then I'll need a table for each bizCat that has user ID's and Document ID's. That might be the best way to normalize the data.

[rgb]

I agree with Andrew. 72 tables is a bit over the top. Why not just assign the 36 parameters to 36 elements in an array, each element being a 0 or 1 for 'off' or 'on'. You could then implode the array and insert it as a string of 0's and 1's into the parameters field of your table. Then search by the pattern of 0's and 1's.

The natural progression from that would be to consider the string of 0's and 1's as a long binary number and use the bindec function to convert it to a decimal number and insert/select etc from the db using that number. (Might be a problem playing with a 36 bit number in a 32 bit computer - you'd need 2 bytes)

[AndrewGSW]

I can't be assigning docs to users, I've got to assign docs to parameters.

If you don't assign docs to users then delete the userdocs table from my outline.

Perhaps I don't follow. If a manager has all 36 then (from my outline):

userparams
____
userid: 12 (managers id) parameter: 1
userid: 12 parameter: 2
userid: 12 parameter: 3
etc..
userid: 12 parameter: 36

for QC specialist:

userparams
____
userid: 20 (QC specialist id) paremeter: 8
userid: 20 parameter: 15
userid: 20 parameter: 22

But perhaps I've not understood.

[rgEffects]

I just had an ahHa moment....
I don't even need a parameter table I just need a parameter list to choose from. I've gone from 72 tables to make this work to 2.

At least that's what I think just happened. I'll give it a dry run and let you know.