If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Design question
I am designing a custom database for tracking various pieces of software we
use at my job. Each user has access to different pieces of software and many pieces of software require a separate username and password for each user. I'm trying to figure out the easiest/best way to do this. I'm using our and Employee table with EmployeeID as the PK and the EmployeeID FK in the Software table. I can either create a Software table that lists a field for each type of software and a separate username and password for each piece of software (which I know is poor database design) OR create a many to many relationship and create another table. The part that's throwing me off is the password/username part. Each person will have several usernames and passwords (one un/pw for each piece of software). Does anyone have any suggestions? I'm using Access 2007. |
#2
|
|||
|
|||
Database Design question
As far as keeping track of the various user names an passwords, you
could add a third (junction) table that woul handle this. For example; tblEmployees ********** EmployeeID (Primary Key) FirstName LastName other attributes of the Employee tblSoftware ******** SoftwareID (PK) Description other attributes of the software tblPasswords (the junction table) ********** EmployeeID (Foreign Key to tblEmployees) SoftwareID (FK to tblSoftware) UserName Password EmployeeID and SoftwareID woul be a composite PK in the junction table. Each record in this table would hold the valid user name and password for any given combination of Employee/Software Item -- _________ Sean Bailey "Carrie" wrote: I am designing a custom database for tracking various pieces of software we use at my job. Each user has access to different pieces of software and many pieces of software require a separate username and password for each user. I'm trying to figure out the easiest/best way to do this. I'm using our and Employee table with EmployeeID as the PK and the EmployeeID FK in the Software table. I can either create a Software table that lists a field for each type of software and a separate username and password for each piece of software (which I know is poor database design) OR create a many to many relationship and create another table. The part that's throwing me off is the password/username part. Each person will have several usernames and passwords (one un/pw for each piece of software). Does anyone have any suggestions? I'm using Access 2007. |
#3
|
|||
|
|||
Database Design question
Carrie
Is there a chance that you actually have a many-to-many situation (one user, many software titles:ne software title, many users)? If so, you'll need three tables to resolve that. Are you saying that each instance of USER and SOFTWARE might have a different [UserName] and [Password] associated with it? If so, then the "third table" (the one that shows valid pairs of USER and SOFTWARE) will also need [UserName] and [Password]. It sounds like you could have one software title (e.g., Microsoft Word 2007) but multiple licenses (one per user). If this is your situation, your "third table" might look something like: trelUserSoftware UserSoftwareID UserID (a foreign key pointing back to the [tblUser]) SoftwareID (a foreign key pointing back to the [tblSoftware]) UniqueUserName (see above) UniquePassword (see above) LicenseNumber (see above) InstallationDate ... (any other facts specific to this user having this software title installed) Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Carrie" wrote in message ... I am designing a custom database for tracking various pieces of software we use at my job. Each user has access to different pieces of software and many pieces of software require a separate username and password for each user. I'm trying to figure out the easiest/best way to do this. I'm using our and Employee table with EmployeeID as the PK and the EmployeeID FK in the Software table. I can either create a Software table that lists a field for each type of software and a separate username and password for each piece of software (which I know is poor database design) OR create a many to many relationship and create another table. The part that's throwing me off is the password/username part. Each person will have several usernames and passwords (one un/pw for each piece of software). Does anyone have any suggestions? I'm using Access 2007. |
#4
|
|||
|
|||
Database Design question
Thanks, Beetle! That's exactly what I needed. I just needed someone to put it
in simple terms for me. "Beetle" wrote: As far as keeping track of the various user names an passwords, you could add a third (junction) table that woul handle this. For example; tblEmployees ********** EmployeeID (Primary Key) FirstName LastName other attributes of the Employee tblSoftware ******** SoftwareID (PK) Description other attributes of the software tblPasswords (the junction table) ********** EmployeeID (Foreign Key to tblEmployees) SoftwareID (FK to tblSoftware) UserName Password EmployeeID and SoftwareID woul be a composite PK in the junction table. Each record in this table would hold the valid user name and password for any given combination of Employee/Software Item -- _________ Sean Bailey "Carrie" wrote: I am designing a custom database for tracking various pieces of software we use at my job. Each user has access to different pieces of software and many pieces of software require a separate username and password for each user. I'm trying to figure out the easiest/best way to do this. I'm using our and Employee table with EmployeeID as the PK and the EmployeeID FK in the Software table. I can either create a Software table that lists a field for each type of software and a separate username and password for each piece of software (which I know is poor database design) OR create a many to many relationship and create another table. The part that's throwing me off is the password/username part. Each person will have several usernames and passwords (one un/pw for each piece of software). Does anyone have any suggestions? I'm using Access 2007. |
Thread Tools | |
Display Modes | |
|
|