(Many to Many) to Many; Design Help

[burntice01]

Hi All!Using a combination of WMI and SQL Server, I am trying to maintain records of users on our network (table: Users), computers on our network (table: Computers), and logon times of specific users to specific computers (table: Records). Since each user can be associated with 0 or more computers, and each computer can be associated with 0 or more users, I am currently using a link table (Users_Computers) to connect the Users and Computers tables. Now here's the problem: For each row in the Users_Computers table, I want to store 0 or more dates that correspond to when the User in that row logged into the Computer listed in that row. I had hoped there was some way to keep this data in the Users_Computers table, but I've hit a wall with designing the best solution to the problem.My current idea on how to store this data looks a little like this:Table: Recordsint Record_IDFK User_IDFK Computer_IDsmalldatetime LogonTimeIf anyone wanted to offer some insight on whether this design is solid, or perhaps an alternative, I would be very appreciative. Thanks!

[packrat]

One question, what's the schema of the Records table? I'd think that it would have columns for computer, user, and date; I'm failing to understand the indended use of the Users_Computers table. As far as the Users_Computers table goes, if it is purely a cross reference of distinct user:computer relations you might consider a table with only those two columns, unless you need a surrogate primary key (record_id) you might be better off without it; with a x-ref table of 2 columns you can easily make a covering index and satisfy lookups in whichever direction is needed.Need more info to provide a more thoughtout answer though.

[burntice01]

Let me clarify.The Users_Computers table is currently what I use to connect the Users and Computers tables. It has two columns, one for the User_ID and one for the Computer_ID, and it's primary key is a combination of these two columns. This table is what allows me to view all the computers associated with a specific user, and in the other direction, all the users associated with a specific computer. My problem comes in when trying to log time stamps of when a specific user logs into a specific computer. I'm trying to figure out if there is an easy way to include just a list of dates with each row in my Users_Computers table, or if I should create a Records table for maintaining what should essentially be a complex primary key of (User_ID, Computer_ID, LogonTime).Please let me know if other information or diagrams would be helpful, and thanks for the response!