Database structure (many to many issue?)

[DNS809904]

Database structure (many/many to many issue??)

Hello,
I am looking for someone's professional opinion to help me clear up some unresolved technical issues in my mind... =P
Ok, I have a contact management database that would seem really straightforward to me except for the fact that I am dealing with 2 primary entities. In this I mean I have an Individual table and Organization table. These two tables have primarily the same field data, such as both having address data, phone (contact info) data. There are some unique fields to one entity that aren't contained in the other entity though, and visa versa. One important issue is that 0, 1, or more individuals can be related to an Organization. Therefore I now have linking tables for IndividualOrganization, as well as linking tables for Phone and Address data.
My primary complication is that since both Individuals and Organizations have address and phone data, the respective linking tables for them contain: an addressID/phoneID key, for the appropriate linking table, with an OrganizationID key and an IndividualID key in each table. So, the way I am thinking is that if the record pertains to an Individual, the appropriate ID will be in IndividualID and the OrganizationID will just be 0, or empty. This brings up issues with primary keys being blank.
I am wondering if there is a better way to do this that I am overlooking or if I am in fact on the right track? I have contemplated combining the Organization and Individual tables into one but that really doesn't seem like the best solution. If anybody has any ideas then I would GREATLY appreciate it. I had a working database the other day but now I am separating the phone and address info out of the primary tables and have really got a mess on my hands. =P If anybody would like to look at my database structure I'd be more than happy to post the back_end and the front_end for you to give me your honest opinion about any trouble areas.
Thanks BIG TIME in advance to any nice souls willing to help out a struggling programmer who doesn't have any close support on this one as I am working alone,
Dana S.

 

[llkhoutx]

Aren't your Organization and Individual tables related (one to many)? Example: Law firms have many lawyers; a single lawyers usually goes by a firm name. Your address table problem is eliminated. However, the Organization can have one set of telephone numbers while the Individual can have another set (direct numbers). Your tables appear to be have a defective design.

Otherwise:

Combine the tables, or use random table autonumbers for each. I use the latter method without problems. With unique random autonumber you won't have a automumber collision provided that you seed the random number generator with Now() everytime the program starts.

I ran a test some time ago with the Now() seed and generated 2 million unique random IDs in a Jet table. I arbitrarily stopped at 2 million. Without the seed, every program restart generates the same sequence anfd creates duplicates.

 

[DNS809904]

My database structure needs further review!

Aren't your Organization and Individual tables related (one to many)? Example: Law firms have many lawyers; a single lawyers usually goes by a firm name.

Actually it was determined that it is *possible* that an Individual could be related to more than one Organization. That is why I felt the need for the linking table for the possible many-to-many relationship. PLUS, I was advised by another member to create the linking table for the instance that there is ZERO organizations linked to the individual.

Your address table problem is eliminated. However, the Organization can have one set of telephone numbers while the Individual can have another set (direct numbers). Your tables appear to be have a defective design.

Does that actually deem the design to be defective? I thought that is again the purpose of a linking table. It is just made more complex relating the phone table with TWO separate tables (Individual and Organization), only using just one foreign key or the other(Ind OR Org) to connect the phone numbers to their respective table entry.

Otherwise:

Combine the tables, or use random table autonumbers for each. I use the latter method without problems. With unique random autonumber you won't have a automumber collision provided that you seed the random number generator with Now() everytime the program starts.

I ran a test some time ago with the Now() seed and generated 2 million unique random IDs in a Jet table. I arbitrarily stopped at 2 million. Without the seed, every program restart generates the same sequence anfd creates duplicates.

Please forgive my lack of knowledge, but I don't see the big difference in using random table autonumbers over incremental autonumbers other than the obvious difference.

I am posting my back-end structure to this message for anybody to check out what I have going on under the hood. (Unfortunately the .zip file is a bit over the 100kb posting limit, therefore please send me a private message and I will be happy to email it to you or put you on my share list and give you access to my yahoo briefcase containing it)

I GREATLY appreciate anybody checking this out and giving me their opinions. If need be, I would even be willing to PAY somebody for their professional assistance in getting my database structure back on the right path as I am working under a timeline and need to get on with my progress! I'm sure many of you can relate!
So till then, thank you very much in advance and I look forward to corresponding with you on this very confusing matter (to me! )
Thanks again,
Dana S.

 

[llkhoutx]

If am individual is related to more than one Organization, add a junction table between the individual and organization tables, with IndividualID and OrganizationmID as to only columns in the junction table. Draw a pickure of it and you'll see how it relates (many to many) Individual and Organization records. Of course, you to have to manually (with VBA code) populate it.

It seems to me that the phone table has to be related to both because they can have different and multiple numbers.

If the OrganizationIDs and IndividualIDs are not unique and random, you'll have referencing collisions unless you use an key indicating which table is the instant parent. With incremantal IDs, Individuals and Organizations will have the same incrementing IDs, unless offset, necessitating more complexity. With random IDs, they'll be unique.

There is a perfect way to do things, but one always strives for perfection and never achieves it. If tables do not have referential integrity and atomic fields, some posters go ballistic.

I think you're on the right track, having identified the issues with your tables. Access handles such complexity in many different ways.

 

[The_Doc_Man]

DNS, this is one man's opinion, but it is based in experience.

The fact that your individuals and organizations both have phone numbers and addressess is of no consequence. They don't belong together in a single table, IMHO.

Normalization rules, broken down to their simplest form, say that things related to each other and not related to anything else belong in their own table. To note that both Organizations and Individuals have certain similar attributes does not matter. They are different entities with respect to your business environment and therefore, the two must be in separate tables.

Having decided that Individuals and Organizations must be separate, it takes no time at all to recognize that Individual addresses, because they are related to individuals and not organizations, must appear in the Individual table. Organization addresses, because they are related to organizations and not individuals, must appear in the Organization table.

A linking table that shows ONLY the Org ID and the Individual ID is perfectly correct in this context. This is exactly how you specify relationships between tables such that 1 table can have 0, 1, or MANY references to a single record in its partner table.

Forcing a combination of the address portions of the two tables into a single table is a big mistake. The data elements don't mesh with each other in number of references or backwards references. As you have pointed out, you have the potential for many-to-many mapping between individuals and organizations. In that case, you would be placing a strain on the table that held addresses to make it contain data for entities that can have multiple relations to each other. The relationships required to define this might be unpleasant to say the least, and downright UGLY if you have to consider the many-to-many part in any detail.

Now, if you want to do something like maybe have a reverse look-up form (give me a phone number, tell me who it belongs to), look at a UNION query between the two tables for that lookup function ONLY. The UNION query might resemble

SELECT Addr1, Addr2, Addr3, PhoneNum, "O", OrgName as OwnerName From Organization ;
UNION
SELECT Addr1, Addr2, Addr3, PhoneNum, "I", FirstName & " " & MiddleName & " " & LastName as OwnerName From Individual ;

You could search this query for matching phone numbers or address parts pretty easily.

 

[DNS809904]

Followup thoughts...

Thanks to llkhoutx and The Doc Man for posting their thoughts on my situation...
llkhoutx, I think we're thinking on the same lines but just in slightly different contexts... the only trouble with one way communication such as this. However, I still value your opinions as it helps me further develop mine.

Doc Man, thank you very much for addressing the issues of my junction tables, specifically with the phone and address tables.

So it looks like the best thing to do is separate the junction tables to having an OrganizationAddress junction table and an IndividualAddress junction table, as well as separate OrganizationPhone junction table and IndividualPhone junction table. This is an idea that I thought would initially make my database more complicated, but at the complexity level it has already reached I can see that it will ultimately make my design and implementation most likely *much* easier to execute.

I did not address every element of my database for message size and understandability concerns, but I see that this same junction table situation will apply for my mailing and seminar tables, used to schedule mailings and seminars, respectively.

I am continually and progressively seeing the need to separate all relations dealing with Individuals and Organizations as they are truely separate entities. As I get deeper into this project it is also becoming apparently obvious for this separation.

Once again thanks to all who provide their assistance with my design and the offer is still out there for me to send you my DB back end for you to examine my complete database design to point out any obvious defects. Feel free to send me a private msg and I will be happy to send it over to you.

Thanks again!
DanaS.

 

[DNS809904]

Thank you The_Doc_Man

Doc Man,
I would like to personally thank you for your input on my database design problem. This is by far the most complex database I have ever attempted to develop and I am under pressure for this project to succeed, in fact it MUST succeed, which is unfortunatley causing undue stress on a young 24 year old man's life! =P

I am sure that you are very busy yourself but I would just like to extend (if not harass! ) the invitation for you(and others reading this) to personally check out my database structure and let me know if you see any obvious defects. I understand if you are too busy to deal with this right now. Again I am more than willing to compensate you financially or in any other way possible.

If I can be of assistance in any way also then feel free to let me know! I look forward to corresponding with you(and others reading this) further.

Thanks again,
Dana Simmelink