Using data from a Tables PK twice

[Dnallov]

I just can't work out a solution to the following...



I have a table with a list of companies, that has many shareholders. As such I have a list of shareholders. The problem is some shareholders may be companies, which would be in the companies table.

In essence, I have two Foreign keys in one table, that I want to link to the same Primary Key in another table.

Any ideas how I can achieve the above?

[ItsMe]

A complicated approach would be to create a lookup table that interpreted the Type of entity. So the lookup table would have three columns, PrimaryKey, ForeignKeyHolder, HolderType.

The PK would be a normal Autonumber. The FK, would be LongInteger and be indexed -- allow duplicates. The holder type would be an Integer and would store values like 1,2, or 3.

Basically, what you would be doing is manually creating a lookup field. But the twist is that you are storing the lookup tables unique identifier (PK) in your other table(s). This is complicated because you will need to incorporate VBA code that would consider an enumeration and then decide which table name (people or company) to include in queries.

It may make more sense for you to squish the person entity and the company entity together, into one table. This would violate the Rules of normalization. I have employed both techniques. You need to weight the pros and cons. Also, there are many variances on how to employ the junction table thing.

[Micron]

What "another table"? Are the companies and shareholders in the same table? They should not be.
Your situation and goal is unclear, but it is possible to link field A and field B in tbl1 to field C in tbl2 in the relationships window using the combo boxes in the relationships dialog, so I'm not sure what your issue is.

[ItsMe]

... so I'm not sure what your issue is.

I see it as the OP has schizophrenic companies that sometimes act like shareholders. I believe this creates issues when an event takes place and a query needs to retrieve information on Company A and its shareholders, when the Shareholders are a combination of people and organizations, e.g. ShaerholderA, ShaerholderB, ShaerholderC, and CompanyC.

[Dnallov]

Thanks again for your help @ItsMe

I'm going to try and implement your suggestions today. However, I thought I would let you look at my Relationships (simplified; removed all the noise), and explain exactly what I am trying to archive to see if there are any (easier!) solutions.



At the centre of this relationship web is "tbl_companies"; every company has shareholders and some of these shareholders are companies (the others would be people, found in the 'tbl_Directors', which is working fine). So I have my junction table 'jct_ShareholderCOM', but now COMP_Shareholders needs a home, and (ideally) the company would be found in the 'tbl_companies' table.

[ItsMe]

I think you are trying to address too much in a single thought. I would break off a smaller piece. For instance, consider something that happens in the course of doing business that will depend on your application. Try to imagine the smallest thing. For instance, a shareholder's meeting. That seems small. Maybe we can break that down even further. Maybe we can address the minutes. OK, maybe we can address the opening minutes, maybe just roll call.

With that idea in mind, do your best to not use any junction tables. For instance, does CompanyDirector need to be a junction table. Perhaps this is an entity in its own right and can stand on its own. For instance, what happens if you rename it tblDirectors and add some attributes. For instance, what about adding some dates to when a Director was voted in and voted out.

Now, what do we do with ShareholderCOM? Is this table so different from the other shareholder table? What would happen if ShareholderDir was renamed to tblShareholders and we added a column/attribute. I would try to get it to work where it is not a junction table. Maybe a solution would be to add a Yes/No column to tblShareholders and naming that attribute, "IsCompany".

Doing this will likely feel like you just went back to square one. But, that's OK. While on square one ask yourself, "What would happen if I placed the Company names together with the Director names, in the same table?"

Think about this, because the alternative is a more complex solution. You may need a more complex solution to support Business Rules beyond Roll Call in a Shareholder's Meeting.
https://www.accessforums.net/showthr...457#post331457

[Dnallov]

Sorry; I should have explained the purpose of the DB.

This is a loan book, not a single company. There are over 100 loans (and growing) so will be 100s of companies (i.e. borrowers) and 1000s of directors & shareholders. The purpose of the DB is for due diligence; ultimately to find out which loan is connect to other loans (i.e 'loan 1' is connected to 'loan 55' because the borrows (i.e. the company) have the same director) I excluded the loan table in my above relationship graph because I thought it might only confuse matters. Below is my (current) relationships in full



I do need the junction tables because some of the data (i.e. is the director active) is different from company to company.

I hope that makes sense; I'm going to play around with all the ideas you have provided, and will return if I need more help (I guarantee I will be back soon ).

[ItsMe]

There is nothing wrong with isolating entities and examining how to Normalize the structure. I would encourage doing so. You need to be prepared to take steps forward and backwards.

Consider this, you are adding a lot of attributes to tables that you are calling a junction table. When you start to do this, determine if you just defined a new entity. Because new business rules will always creep up, I will include an Autonumber to my Junction Tables. If I was correct in assuming I was creating a junction table, the Autonumber will never be utilized. However, down the road, I may discover the junction table evolves into an entity. So, post #6 still applies and so does post #2. If you can make it work, you may want to place Company and Director together, in one table. Otherwise, you will be managing a complex system. Normalize till it hurts, de-normalize till it works.