Credentials Store (1 Viewer)

Hi all,

As discussed in my previous thread (http://www.access-programmers.co.uk/forums/showthread.php?p=1472956&posted=1#post1472956) I was trying to create a concatenated primary key. After some assistance a composite key was what I required.

I am trying to achieve the following - 4 tables which include Site Information, Software Information, Database Information and Credential Information. I can successfully create the appropriate relationships between Site Information, Software Information and Database Information. For instance there may be three pieces of software against a site to which two of them has one or more attached databases. Example:

SiteInformation
-----------------
SiteID - 1

SoftwareInformation
-----------------------
SiteID - 1
SoftwareName - Application 1

SiteID - 1
SoftwareName - Application 2

SiteID - 1
SoftwareName - Application 3

DatabaseInformation
-----------------------
SiteID - 1
DatabaseName - DB1
SoftwareName - Application 1

SiteID - 1
DatabaseName - DB2
SoftwareName - Application 1

SiteID - 1
DatabaseName - AppDB
SoftwareName - Application 2

Depending on the application the credentials may be stored in either the application or to one of the attached databases to that piece of software. How would I go about adding in the fourth table here for credentials? There will be no difference in terms of what is trying to be stored in this table (usernames) however I need to create a relationship for this Credentials table between SoftwareInformation and DatabaseInformation so that I can ascertain where the username relates to - database, or software.

I have tried to create this fourth table with relationships between both SoftwareInformation and DatabaseInformation with little success. When I populate the HeldAgainst field in CredentialInformation it gets upset that the supplied value doesn't exist in one of the two relationships (SoftwareInformation/DatabaseInformation).

I think I can see how this should work, two credential tables, one for DatabaseInformation and the other for SoftwareInformation. I just need some clarification if this is the correct way to go about it.

I've attached the database I've been working with to this thread.

You need to rethink your relationships. You've over-related your tables. Specifically, DatabaseInformation and CredentialInformation.

1. You should add autonumber primary keys to your tables. Those are a better way to relate tables than plain numeric and especially text fields. [SoftwareName] shouldn't be the primary key of SoftwareInformation, you should have an autonumber primary key like [SoftwareID]. Same goes for all tables--use an autonumber primary key.

2. DatabaseInformation shouldn't be related to SiteInformation. If all databases will be linked to a record in SoftwareInformation, then that will do the trick. You can link DatabaseInformation to SoftwareInformation and get each the SiteID. Same goes for CredentialInformation...

3. ...However, I'm not certain how CredentialInformation fits into this. Is Credential Information related to a database or Software? From your explanation it sounds like its related to DatabaseInformation. So, that means you should have an autonumber primary key in DatabaseInformation that you use as a foreign key in CredentialInformation to link them.

I just read the other post and you didn't give those helping you enough information. Actually jdraw sniffed out your need for autonumber primary keys, but nothing came of it.

Honestly, while those helping you gave good advice with the information you provided, I would disregard that entire post. In most, including this, instances Autonumbers are the way to go for creating primary/foreign keys. You need to add those to this database and leave composite keys behind.