How to Allow Username Record Field to Be Foreign Key from Two Different Tables (1 Viewer)

MCCDOM

Registered User.
Local time
Today, 07:41
Joined
Oct 30, 2014
Messages
84
Hi All,

Hope you're well.

I've been scratching my head with this one. Basically I am trying to create a table that will store the login credentials for the different cloud storage accounts (tblCloudStorages). The username for the Cloud Storage will be an email address but it could either be an actual email account or an alias email address tagged onto an actual email account. Because of this I have two seperate tables, one for actual emails (tblEmailAddresses) and one for the Aliases (tblAliases). For managing these I have created a junction table called (tblEmail_Aliases) where I store the foreign key for each email/alias to link them together. I've attached a screenshot of my relationship table to help you better understand.

My question is how would I go about referencing either the EmailID or AliasID to be used for the Cloud Storage Username? I tried linking to the junction (tblEmail_Aliases) table but just end up with both email and alias assigned to the same Cloud Storage details. I also tried a junction tables for Alias+CloudStorage and Email+CloudStorage. Again, same result, just ended up with both emails assigned to the same Cloud Storage.

Any suggestions and advice would be greatly appreciated please.

Many thanks,

Dom
 

Attachments

  • Email Alias Relationship.PNG
    Email Alias Relationship.PNG
    10.1 KB · Views: 206

Minty

AWF VIP
Local time
Today, 07:41
Joined
Jul 26, 2013
Messages
10,355
If you had a flag in your tblEmailAddresses (flgAlias), and a "ParentEmailID" Field, you could store them all in the one table and reference back to the primary email address no matter how many aliases someone had?

TBH you don't really need the flag, as if it has a ParentEmailID in it, it is an Alias.
 

MCCDOM

Registered User.
Local time
Today, 07:41
Joined
Oct 30, 2014
Messages
84
Hi Minty,
Thanks for your quick response. When you say flag, do you mean like a Yes/No field for whether it's an alias or not?

What if I added the record Alias to tblMailboxType that way I could use a query to filter out the alias accounts or vice versa.

Or would the ParentEmailID be a more reliable solution? So essentially I would add a field called ParentEmailID and this would then contain the main ID of the tblEmailAddresses next to any accounts that are an alias? Is that what you're describing?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:41
Joined
Feb 28, 2001
Messages
27,001
I agree with Minty that you should merge the two tables into one since they would have the same key (a User ID) and the same purpose.(hold supplemental info on users). The fact that there is extra information COULD lead to that join situation, but it would be better if you just had a table that sometimes contained zeros or zero-length strings when there IS no extra information for that class of account. The flag that Minty suggested COULD be a Yes/No flag, but if there is something else that is ALWAYS zero for one class and NEVER zero for the other class, a simple test would work, like <>0 or <>"" depending on the field's data type.
 

MCCDOM

Registered User.
Local time
Today, 07:41
Joined
Oct 30, 2014
Messages
84
Thanks Minty, that is what I had envisaged from your original description. This will certainly make it easier to manage.

Also thanks The_Doc_Man for your comment.
 

JMongi

Active member
Local time
Today, 03:41
Joined
Jan 6, 2021
Messages
802
If you TRULY have a SUPERTYPE - SUBTYPE arrangement, than you can use separate tables with a 1:1 relationship on the primary key but many would rather deal with a bunch of empty fields than set up their table structure that way.

But, if @Minty idea works for you, than I would go with it for sure!
 

Minty

AWF VIP
Local time
Today, 07:41
Joined
Jul 26, 2013
Messages
10,355
The beauty of the self-referencing table structure is that this isn't a 1:1 relationship. You can have multiple aliases.

jt@saturdaynightfever.mov could be another alias for record 3

Or you can reference another previous alias so the above could be an alias for record 5, but then the queries get a little more interesting to write, as Access doesn't handle recursive structures/functions very well.
 

JMongi

Active member
Local time
Today, 03:41
Joined
Jan 6, 2021
Messages
802
The need for true supertype - subtype arrangments is rare. Rare enough, that it gets rejected out of hand sometimes because its usually a faux-pas. But it can be useful.

However, it doesn't appear this is the case here. This is actaully a nice example of the usefulness of a self-reference table (Access grumblings not withstanding).
 

MCCDOM

Registered User.
Local time
Today, 07:41
Joined
Oct 30, 2014
Messages
84
The Email Address and Alias Address is a many to many relationship, which is why I split them into separate tables originally and used a junction table.
How would I represent this many to many relationship within the self referencing table please?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Pardon me for jumping in; but if I understand it correctly, I would just have one table for all the email addresses and a separate table to show which email is the base with corresponding the alias.

tblEmails
EmailID
Email

tblAliases
ID
BaseID
AliasID
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 03:41
Joined
Feb 19, 2002
Messages
42,981
How would I represent this many to many relationship within the self referencing table please?
You can't. A many-to-many relationship REQUIRES a junction table. You can use the same table on each side of the junction so you can have 1 email table and the junction table connects email1 to email2 and indicates which is the base and which is the alias.

Are you just trying to be flexible or do you really have a situation where there can actually be a m-m between base and alias? That just does not compute. Most email systems allow multiple alias' for each address but having the same alias relate to multiple base addresses would cause duplication.
 

JonXL

Active member
Local time
Today, 02:41
Joined
Jul 9, 2021
Messages
153
I gotta ask... what are you going to use these for? The title and OP indicate these are to be usernames. But don't usernames need to be unique or at least (and perhaps more importantly) tied to a user and not just to one another?

By the relationship you are saying you want to set up your users could all have example@example.email as an "alias" they use for their login... perhaps that's allowed?

But even if that's okay, to the second issue, though, I sense you're missing a key relation for all your emails - that which goes to your users. If you add a UserId to the single-table solution, then the rest won't even matter since you can get your user from whatever email they happen to use for their username without having to retrace some inane email primacy hierarchy... And isn't that what you really want to identify when a username is passed?

Forgive me if I've completely misunderstood your requirements though.
 

Minty

AWF VIP
Local time
Today, 07:41
Joined
Jul 26, 2013
Messages
10,355
Base Address
Alias Address

email1 has info and support as an alias

The alias info@me.com has email1 and email2 as base addresses.
This is a very confusing description.
Based on the above what would your expected result be assuming you only want to provide a single email address as an output.
If that isn't your goal, then we will need to see two sets of data a fuller example of the above and what you want as a output based on that data.

Perhaps Alias is a confusing description, perhaps SendACopyToEmail would be better description of what you are trying to achieve?
 

MCCDOM

Registered User.
Local time
Today, 07:41
Joined
Oct 30, 2014
Messages
84
Sorry if I've caused any confusion. I will try and describe this as best as I can.

The goal for the database is to keep a record of everything at work, from a list of email address, login credentials for emails, websites, cloud storage sites, etc. and many other useful bits of information like who has what laptop but that last one is not important to this query.

As it stands, I have tables for the following:
DescriptionTable Name
StafftblStaff
Email AddressestblEmailAddresses
Email PasswordstblEmailPasswords
Mailbox Type (I.e Exchange, IMAP, 365)tblMailboxType
Cloud Storage Credentials (Dropbox, OneDrive)tblCloudStorages

The Emails
I have a long list of paid email accounts compromising of Exchange, IMAP, 365. Then there's also the alias email. This is a free email address which can be bolted on to a paid email. So for example, john@example.com (paid email) can have the alias info@example.com tagged onto his account, so any emails sent to info@ will actually just end up in John's inbox. In addition to this though is the fact that jane@example.com (paid email) can also have info@example.com (alias) tagged onto her account. So info now has two paid accounts assigned to it. To make it many to many, jane@example.com (paid email) could also have support@example.com (alias) tagged onto her account giving her ownership of two aliases.

I've then got the tblMailboxType which contains what kind of email account it is: Exchange, IMAP, 365 Plan 1, 365 Plan 2. Each type also has a cost assigned to it, so I can then do a monthly billing report breaking it down for each office.

The Cloud Storage
I've got a variety of cloud storage accounts setup with dropbox and onedrive and they use an email address for the username. Now that email address could either be a paid email address or a free tag on alias account. I would therefore like to pull the relevant email from tblEmailAddresses to be used for the Username field of tblCloudStorage.

I hope this explains a little bit more in detail what I am trying to achieve. I've also attached a screenshot of my current table relationship in Access if that helps you visualise a bit better.
 

Attachments

  • Access Relationships.PNG
    Access Relationships.PNG
    15.3 KB · Views: 424

Users who are viewing this thread

Top Bottom