How to make a single user app to a multiuser app?

Question

A few years ago I created a simple app to track my daily status. Nothing fancy, you just add a row, it sticks in the date, I would put start time, end time and what I did. Everyone laughed but I kept at it for 3 years. Now the boss sees how 'organized' I am (it's just a long spreadsheet view) and wants me to extend the app for the rest of the team. He would also like to see the list view we have so this would require making it a shared db somehow. I am using SQlite now since it's all on my machine so how do I extend this app.

I am looking for ideas so all are welcome.

Update: I guess my question is more about having a db server rather than the embedded db I have now. Won't I need to manage concurrency, etc?

Answer 1

Ideas:

• Introduce a user identity number to the db records (or a string representing their unique username) to stamp the rows with who they belong to.

• Add a login screen, and if everybody trusts each other than don't require a password - just name selection.

• If you have business object, put a .UserId property on them to track the current user.

• When a user "logs in", keep track of their user id to filter the database data using WHERE userid = 5

Mainly the structure of your program stays the same and you introduce the new requirement of tracking the user id for the current user and filtering everything else appropriately based on that user id to enforce proper boundaries.

Update:

If you old db table looked something like

TaskId | Start Time  | Length   | Description |

1        10:45 Feb 3   20 min     finish the code
2        10:45 Feb 5   5 m        delete comments
3        10:45 jan 8   2 hr       meeting

then my idea proposed something with an addition 'usreid' column like:

Userid | TaskId | Start Time  | Length   | Description |

10     | 1        10:45 Feb 3   20 min     finish the code
15     | 2        10:45 Feb 5   5 m        delete comments
30     | 3       10:45 jan 8   2 hr       meeting

and you might have a supporting user table like:

UserId | Username | Full name
10       bob1     | Bob Smith
15       mnmn     | Guest user
30       sarah55  | Sarah Baker

Answer 2

In the long term, you should probably do what jdk suggested, but in the meantime, as a stopgap solution, you could have a separate SQLite database for each user.

Answer 3

These are the steps I would take:

1. Create a Users/People table to store the individual users. Columns may include: Name (First and Last), Position, PhoneNumber, etc). Each row should have a unique ID (i recommend either a uniqueidentifier or IDENTITY int column.
2. Add a column to the table that contains your status data. This should be of the same type as your unique ID field in the Users table. if the column in users is called ID, then UserID is a good name.
3. Create a foreign key relationship between the primary key of the Users table and the new column in the 'status' table.

HTH

Answer 4

you need to move the app to a centralized db server. Preferably a full blown database. This will handle concurrency for you.