What design should I use?

Question

I am creating a program that will monitor several servers and computers.

A user can create an account and have multiple computers in his account that will be monitored when the user installs the client on it.

When the client is running for the first time, the user can input their account info and it will be saved encrypted for automatic login. After that the program will send important data to the database that only the user can read within his account.

As I am using Mysql.net connector in my vb.net project I would still want to use MySQL as my database.

At the moment I have one DB with the current tables: accounts, computers.

The problem that I am running into is the following:

At this moment the design is very flat. User IDaccount is connected with IDcomputer. But this works only for 1 computer per account. I need a way to link multiple computers to one user account. How should I go about this?

I was thinking of using a separate table that holds the computer IDs with the correct user account. Am I going in the right way?

The accounts table will hold the following info:

IDAccounts
Name
Email
Username
Passwordhash
Salt

computers table will hold the following:

IDComputers
MachineID
Hostname
WANIP
LocalIP
OS
CPUPercentage
RAMFree
RamUsed
Appversion
FreeSpaceC

I'm learning on the job. I just need to know how I can link the user account with multiple computers. I will figure out the rest, I just need a pointing in the right direction.

Answer 1

If you want to have a computer be assigned to more than one user, you're going to need a junction table between the two to store the relationships. This was not stated in the requirements but it seems like a logical approach given the project scope. 1. Account 2. Computers 3. Account_Computer_Assignments. The third one is your junction table that stores the relationship between the other two. It's primary key would consist of IDAccounts and IDComputers using your naming convention. A foreign key relating back to their parent tables would also be advisable.
This structure will also allow for multiple users to link to the same computer. I don't know if that is the intended behavior but it will allow for that with this design.
If that is not intended, and a single computer can only be monitored by one and only one user, then a single field on the Computer table for the AccountID is all that is needed. No junction table.