Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Best way to start a comparison list.

    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?

  • #2
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    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
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    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.
    Last edited by rgEffects; 12-15-2012 at 11:48 PM.

  • #4
    rgb
    rgb is offline
    New Coder
    Join Date
    Jul 2011
    Posts
    20
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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)

  • #5
    Senior Coder
    Join Date
    Apr 2011
    Location
    London, England
    Posts
    2,120
    Thanks
    15
    Thanked 354 Times in 353 Posts
    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.
    "I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
    Validate your HTML and CSS

  • Users who have thanked AndrewGSW for this post:

    rgEffects (12-16-2012)

  • #6
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •