table design suggestions

[edpatterson]

I would like to create a simple database to track administrators of local machines. So far I have a collection of text files which contains the administrators.

Example:
Machine1.txt
Administrator
Helpdesk
Imaging
Machine2.txt
Administrator
Helpdesk
Smith-John

Each text file is updated at login.

I was thinking of one table for machines, another for users and a 3rd for machine and users, one machine and user per record. So with the example above machines would have 2 records, users would have 4 and the 3rd would have 8.

Is there a more efficient way that a non-database head could understand?

Thanks

 

[foxdev]

Good instincts, edpatterson. That's typically how it is done, with a "linking table" (MachinesAdmins) containing the foreign keys to the other two tables.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[edpatterson]

OK, I now have the 3 tables created and can insert the data programatically. Here is how it works so far:
VB.Net 2008
Get a list of all the files in the directory. Pull out the machine name and add it to computers, increment a counter variable.
Open the file, read a line at a time. check the value of the line (userName) against an arrayList. If the name is not in the list insert it into the users table and the arrayList.
Query the Users table for the userID, insert both the value of the counter variable and the userID into Administrators.
I can get the computerName or userName from their respective tables from making multiple queries.
What I would really like to do is have one huge honking query do it all in one step.
The user interface (will only be seen by me so it is butt ugly) has two list boxes, one with computer names the other with user names with another listbox below. When a computer is selected the users associated with that machine are displayed, when a user name is selected their associated machines are displayed.
I have to admit, this is kinda fun. Good mental workout.

[tt]
Users
userID int identifier
userName varchar(20)
Computers
computerID int identifier
computerName varchar(20)
Administrators
computerID int
userID int
[/tt]
Ed

 

[foxdev]

What I would really like to do is have one huge honking query do it all in one step.

Ed, could you clarify what you'd like the query to do?

There may be some that say your Administrators table should have its own single-column primary key (AdministratorsID), even though it is not required for your purpose.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[ESquared]

Please don't make an Administrators table separate from a Users table. Put a column in your User table that identifies the primary role. If you have to support multiple roles per user, it will be different. If you have to store more information about administrators than users, use the supertype/subtype design pattern where the Administrator table holds only those extra columns not common to all users, where the User - Administrator table relation is 1 to 0-or-1. 0 if the User is not an administrator and 1 if it is. The subtype Administrator table would share primary keys with the Users table, and you couldn't insert to the Administrator table without first inserting to the User table and getting a primary key value.

Also, instead of the User being defined in a role, it may be only the relationship which defines the role, in which case the user/administrator flag would be in the join table rather than the User table. You might even end up with the column in both tables, one to indicate the primary/default role of the user and one to indicate the role of that user in a relationship with a machine. This would allow an administrator to have a user-relationship with a machine (which is not unreasonable) and a user to have an administrator-relationship with a machine (and I can imagine scenarios where that would be the case).

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[edpatterson]

Ed, could you clarify what you'd like the query to do?

I'll go one better and give the soup to nuts explanation.

I have an active directory installation with ~2500 users. I needed to push out an update and did not want to create yet another GPO. There is a domain account that was supposed to have administrative rights to all of the 'local machines'. I started getting calls about path not found errors. The only way this could happen would be if the domain account was not in the local administrators group.

I added a couple lines to the login script to create a file for each machine listing all the local administrators. <machine.txt>

Being basically lazy I did not want to manually look at a couple thousand of files so I decided to put them into a database.

I now have the database built, 3 tables: Users, Computers and Administrators. Users and Computers have an Identity field (primary key) and a Name field (userID, userName and computerID, computerName) Administrators has computerID and userID both have foreign keys pointing back to their respective tables.

The user names and computer names are displayed in their own listboxes. Selecting a computer displays all of it's administrators, selecting a user displays all of the computers the user has administrative rights to in a third listbox.

A very kind SQL guru (in my humble opinion) gave me the solution(s). I was trying to get UNION to work, turns out I did not need it.

Computers
[tt]
-- get computers that user has administrative rights to
select computers.* from computers, administrators, users
where
computers.computerID = administrators.computerID and
administrators.userID = users.userID and
users.userID = <computerIDhere>
[/tt]
Users
[tt]
-- get administrators of specified computerID
select users.* from users, administrators, computers
where
users.userID = administrators.userID and
administrators.computerID = computers.computerID and
computers.computerID = <computerIDhere>
[/tt]
Just a little more code cleanup and it will be done. A nice little single use utility.

Ed