Concatenated Primary Key

[xPaul]

Hi all,

I have two tables and their format is as follows:

SiteInformation
-----------------
SiteID - PK

DatabaseInformation
-----------------------
SiteID - FK
UniqueDatabaseID - PK (this is concatenated on the form using SiteID+Databasename)
DatabaseName

This works well and allowed me to have a one to many relationship (essentially allowing multiple DBs of different names per site). The issue that I am facing is that when my SiteID is changed in SiteInformation it is pushed down through Cascade Update, however my UniqueDatabaseID is still formatted with the old value of SiteID - it doesn't get updated as there isn't a relationship here.

Aside from creating SQL commands to update this if the SiteID is changed in SiteInformation is there any other way to go about this? For example this would be perfect - creating a cascade update relationship for DatabaseInformation.UniqueDatabaseID from DatabaseInformation.SiteID and then concatenating it with DatabaseInformation.DatabaseName.

 

[sneuberg]

I think you should make the combination of SiteID and Database name the primary key and get rid the concatenated field. Then you won't have this problem. Furthermore it will make it faster if you end up with a lot of records.

 

[sneuberg]

The attached database demonstrates what I meant in my previous post. One thing I find a bit confusing is that when you create a composite index it doesn't offer the option of making it the primary key when you first make it. You have to close it and then reopen it to get that option.

 

[xPaul]

Huh. Never knew that. So even if each individual site has the same database name its perfectly acceptable, however when adding a database of the same name it flags it as a duplicate?

 

[sneuberg]

Huh. Never knew that. So even if each individual site has the same database name its perfectly acceptable, however when adding a database of the same name it flags it as a duplicate?

Yes when adding a database of the same name to a site of the same name it flags it as a duplicate. It works the same as your concatenation scheme. You can try it for yourself in the demo database I attached to my previous post. In this demo I also set up the relationship to cascade updates so you can see that work too.

 

[xPaul]

That's brilliant and is exactly what I want. Just need to redesign what I've been rocking for a little while now.

Say for talks sake I wish to extend this out to a third table (for example recording usernames that have access to a database) using the design you just mentioned, would I design it in the following way, is this how it would scale?

SiteInformation
-----------------
SiteID - PK
SiteName

DatabaseInformation
-----------------------
SiteID - PK/FK
DatabaseName - PK

DatabaseUsernames
-----------------------
SiteID - PK/FK
DatabaseName - PK/FK
Username - PK

 

[jdraw]

XPaul,

Many Access developers use an autonumber as a Primary Key for their tables. Even when they have junction (mapping, linking..) tables, they will create a surrogate (meaningless unique number) key. Then, to ensure there are no duplicates in such tables, they will use a unique composite index and use an error handler to watch for error 3022 (attempt to add a duplicate..) and present a more user friendly message to the user.

Good luck with your project.

 

[sneuberg]

That's brilliant and is exactly what I want. Just need to redesign what I've been rocking for a little while now.

Say for talks sake I wish to extend this out to a third table (for example recording usernames that have access to a database) using the design you just mentioned, would I design it in the following way, is this how it would scale?

SiteInformation
-----------------
SiteID - PK
SiteName

DatabaseInformation
-----------------------
SiteID - PK/FK
DatabaseName - PK

DatabaseUsernames
-----------------------
SiteID - PK/FK
DatabaseName - PK/FK
Username - PK

It should scale fine. I don't know how many fields can be in a composite index but I'm sure it's way up there.

JDraw's approach is seen a lot and I think it is seen at lot because Access gives you an Autonumber as a default when you create a table. I think encourages crappy design as I suspect a lot of people forget about the second step of adding the index to ensure data integrity. Also another disadvantage to these surrogate keys is that you end up with junction tables with no meaningful information. So you end up have to make queries just to see what data these keys represent which is a pain when you are just trying to track down a problem.

On the plus sign if the rules for the primary key change in a table it is easier to make this change if the table has a surrogate key. Also it makes the relationships look a bit cleaner. If you extend your database to include a DatabaseUserNames table there will be two lines between the DatabaseInformation table and the DatabaseUsernames table in the relationships. With surrogate keys there would only be one line.

There are various opinions on this topic. If you want to be better informed google "surrogate key debate"

 

[xPaul]

On the plus sign if the rules for the primary key change in a table it is easier to make this change if the table has a surrogate key. Also it makes the relationships look a bit cleaner. If you extend your database to include a DatabaseUserNames table there will be two lines between the DatabaseInformation table and the DatabaseUsernames table in the relationships. With surrogate keys there would only be one line.

The relationships are now as follows:

SiteInformation.SiteID PK - PK/FK DatabaseInformation.SiteID
SiteInformation.SiteID PK - PK/FK DatabaseUsernames.SiteID
DatabaseInformation.DatabaseName PK - PK/FK DatabaseUsernames.DatabaseName

Which gives me an intermediate relationship type with no chance of enforcing referential integrity, cascade update, cascade delete, stating "No unique index found for the referenced field of the primary table".

Unless I'm designing this wrong or I've ran into an issue, I'm not sure.

 

[sneuberg]

I've added the DatabaseUsernames table to the attached database. Please look at the indexes and the relationships. If you have it like this I don't think you will have the problems you talk about.

If you can't get this working right could upload a copy of your database with at least these table.

 

[xPaul]

Ah, okay, I was doing it totally differently - the SiteID of DatabaseUsernames was looking towards SiteID of SiteInformation instead of SiteID of DatabaseInformation, I believe I done everything else the same though.

Going to study this tomorrow in depth to fully understand it and how you have done it, but in the meantime, thank you ever so much.

 

[xPaul]

sneuberg, I looked at this further and I now know how to set up a composite key and I've been able to expand it further. This time including SoftwareInformation.

So I have the relationships currently set up that it goes SiteInformation - SoftwareInformation - DatabaseInformation. I reckon that since some applications don't have a database and thus store credentials internally a record isn't required in DatabaseInformation, however for those applications which have a database a record in DatabaseInformation is required with a related record showing which piece of software links to the database.

My issue that I am encountering is that I'm now trying to create a 'store' of sorts, CredentialInformation, between SoftwareInformation (for those bits of software that do not have a database) and DatabaseInformation (those that do) however I'm running into either issues or lack of knowledge...

Would it be best to have two credential tables? Each having a relationship with their related table (DatabaseInformation or SoftwareInformation), or should it be a one stop shop type of thing whereby it is bringing all credentials together in one location?

I've attached the database you provided and how far I have got. Hopefully what I am trying to get across and my end goal is evident enough.

 

[sneuberg]

I'm no longer able to understand what you are doing. Since this has evolved into something more than a question about composite keys and seems to be more of a data structure problem I'm going to suggest you start a new thread with a title that better reflects your current question. It will hopefully gain a broader attention that way. I suggest you attach this database to the post with some sample data in it and write up a description of what you are trying to accomplish.