Help with db design

I am trying to self learn sql and databases by small projects around the office. I want to have a database to keep track of accounts and passwords for different machines. The data will be:
machine name
account name on a machine
password for a particular account on a specific machine
and who will have access to a specific password to a specific account on a specific machine.

I have 4 tables, user, machine, account, & password. each table has a primary key which is an integer and the data, i.e. machinename, id1. I understand about queries pulling data from each table, but am lost trying to put everything together.

Am I on the right path for something like this or are the tables wrong? Any ideas are appreciated..

noob2sql

OK you've got the basics.

Start with the table that are unique and that stand alone.

these are Machine and User

tblMachine
Machine_ID (PK)
Machine_Name
....

tblUser
User_ID (PK)
User_FirstName
User_LastName
....

Now let's take a look a the rest of the tables you need.

A Machine can have multiple Accounts (this is called a one-to-Many relationship; One machine - Many accounts) The typical way of building this is like so:

tblAccount
Account_ID (PK)
Machine_ID (FK)
Account_Name
Account_Password

Next is the fact that an Account can have multiple Users and a User can use multiple Accounts. (this is call a Many-to-Many relationship) the typical way to build this is with an association table like so:

tblUserAccountAssociation
Association_ID (PK)
User_ID (FK)
Account_ID (FK)


(PK) mean this field is the Primary Key for the table.
(FK) means this field is the Primary Key in another table. It is considered a Foreign Key (Imported) in this table.

Hopefully this makes sense.

Have fun!

Excellent, thanks for the info. I was able to create the tables from your design and populate with some small data. The query I wrote does exactly what I needed it to do with equi-joins. The key was the UserAccountAssociation table. The data input there ties the user with an account which is also ties to a machine name. So far with just a little test data it seems a lot of work, but I imagine when the data gets larger, the benefits of using a db will be large.

I can't tell you how long I have racked my brain on this, but to see the correct data returned from the query - I am thrilled at the outcome

Thanks again for the help!!!

One more thing. This design assumes that most of the accounts will be machine specific. If you have an account that is used for multiple machines then you will have to creates account records for each machine. This isn't a big issue if there are only a few of duplicate accounts. You just need to remember that if you need to change one you'll need to change all.

Now if about 50% of the accounts are shared across machines then you have a Many to Many to Many (Accounts, Machine, users). There are a couple of ways of doing this and it depends a bit on what you will accessing it all the time. However one solution is the following:

tblAccount
Account_ID
AccountName
AccountPassword

tblMachineAccountUser
Assoc_ID
Machine_ID
Account_ID
User_ID

I don't know that you have this situation, but I figured I'd mention it.

Have fun!