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.
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.
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
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.
It’s a though nut to crack…
