SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Sep 2009
    Location
    Stockholm, Sweden
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question DB design for individual modifications to template rows

    I have an interesting situation that I just can't figure out how to design in a good way that will use the system efficiently.. maybe you can? It's a though nut to crack..

    The situation is this:

    I have a text box (php page), where users can enter a string and it will search the DB for matches of the string.

    I have a table in the DB called searchResults. Looking like this:

    searchResults
    id nr text
    1 1 Pear
    2 2 Europe
    3 3 Computer
    4 4 Balloon
    5 5 Car
    6 6 Fish

    Results are shows as, after a search: "2 - Europe"

    Now.. the tricky part, each individual user on the site.. 100s of them.. should be able to modify theese search results individually. Ie change the "nr" and / or "text". If a user makes a change it should only be visible to themselves, for the other users, unless they change something, it should look like the above example still. Also they can add more rows and / or delete existing ones.

    I do not want to duplicate the base template of rows for each user, in the example above it's only 6 rows but in reality it's about 2000 template rows in the live system.

    Any ideas on how to design this? Any help is much appreciated.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your question is pretty obscure

    please explain "template rows"

    please explain the purpose of the "nr" column

    how does a template row relate to a search? what kind of search returns a number?

    how do the users figure in to this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2009
    Location
    Stockholm, Sweden
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    your question is pretty obscure

    please explain "template rows"

    please explain the purpose of the "nr" column

    how does a template row relate to a search? what kind of search returns a number?

    how do the users figure in to this?
    Alright, sorry for the obscurity Let's see if I can shed some light on this.

    please explain "template rows"
    = The original rows in the DB, as in the example, the 6 rows already there

    please explain the purpose of the "nr" column
    = It's just a number that users should be able to change and it needs to be there

    how does a template row relate to a search?
    = they are the originial ones, if the users hasn't changed the "nr" or the "text" that is what they will see in the searcg

    what kind of search returns a number?
    = all of them.. if they search for fish for example, the result they will see is "6 - Fish"

    how do the users figure in to this?
    = well, the users have the own accounts, and they should be able to change the search results if they want to. Example one user might change the "text" in row 6 to "monkey". So when he searches it will say "6 - Monkey" instead of "6 - Fish". However, for all other users it should still say "6 - Fish" because they haven't changed anything. Likewise one user might choose to delete one row entirely so only he doesn't see it.. and another user might choose to add a new row that only that user will see.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i don't get it

    i don't understand how a user can search for fish and get back monkey

    also, i don't see how to approach this if you furthermore stipulate "I do not want to duplicate the base template of rows for each user"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Sep 2009
    Location
    Stockholm, Sweden
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, the user will get back monkey if he makes a change to fish...

    This is the problem that I'm trying to solve.. my thinking so far is to have another table called changes with the columns:
    id userId templateID nr text

    And if a user wants to make a change to the template rows.. the change will go into the change table and when they make a search, the script will get the values from the template table and then look into the change table and make the necessary changes to the resultset.

    Example

    table_Template:

    id nr text
    1 1 Pear
    2 2 Europe
    3 3 Computer
    4 4 Balloon
    5 5 Car
    6 6 Fish

    table_changes:
    id userId templateID nr text
    1 547 6 4 Monkey

    so if user 547 makes a search for anything (*) he will get the following result:
    1 - Pear
    2 - Europe
    3 - Computer
    4 - Balloon
    5 - Car
    4 - Monkey

    where as all other users, which haven't made a change will get:
    1 - Pear
    2 - Europe
    3 - Computer
    4 - Balloon
    5 - Car
    6 - Fish

    Does this clarify?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by samuelf View Post
    Does this clarify?
    more or less, except the part about what possible application this scheme would have in the real world

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Sep 2009
    Location
    Stockholm, Sweden
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Hehe, good question. This is for a bookkeeping software. Every company has an account plan for bookkeeping, about 2000 rows is a standard plan. Instead of replicating this plan for every company, 10.000 companies make a lot of rows... I'd like to have a template and let the companies make their changes without affecting other companies and without replicating the whole account plan.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ah, i see

    i think you might have started out with that, rather than go through all the fish and monkeys folderol

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Sep 2009
    Location
    Stockholm, Sweden
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Will keep that in mind for next time... until then, any suggestions?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    your idea in post #5 should be okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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