How to associate and disassociate information

[mrjoshuaw]

Ok, blanket statement, not new to Access but have not touched it in years and that was self taught so I am sure there were lots of bad habits learned!



Now the question:

I am building a database with multiple tables that are related by id numbers for each table, for example:

tblBusinessCustomer: CustomerID Primary Key
tblBusinessServiceAddress: ServiceAddressID Primary Key

I have the CustomerID also in the tblBusinessServiceAddress and it is related to the tblBusinessCustomer as: tbleBusinessCustomer"CustomerId" --one to many --> tbleBusinessServiceAddress"CustomerId".

So I have a customer that gets service at a specific address, but then moves to a different location while keeping the same service. What I would like to do is have that customer "disassociated" from the initial address and then "associated" to a new service address. That way I could run a query against all addresses that HAD service but do not have anyone there currently (this would be run against the tblServiceAddress I presume).

Sorry if it is confusing, let me know if clarification is in order, as well as any more information.

[jzwp11]

Welcome to the forum!

You say that a business customer can have many service addresses:

tbleBusinessCustomer"CustomerId" --one to many --> tbleBusinessServiceAddress"CustomerId".

But, let's say that your customer leaves an address and moves to a new address. Now a new business moves into your customer's old address and wants to be your customer and thus signs up for a service. That basically says that an address can have many business customers: another one-to-many relationship. Assuming that this scenario is indeed possible, then your current table structure and your desire to monitor where services were rendered previous to a move would not be possible.

When you have 2 one-to-many relationships between the same two entities (customers and addresses in this case), you have a many-to-many relationship which requires a junction table.

tbleBusinessCustomer
-pkCustomerID primary key, autonumber
-txtCustomerName

tblAddresses
-pkAddressID primary key, autonumber
-txtAddress
-txtCity
etc.

tblBusinessCustomerAddresses
-pkBusCustAddrID primary key, autonumber
-fkCustomerID foreign key to tblBusinessCustomer
-fkAddressID foreign key to tblAddresses
-dteEndEffective (a date field to say when the indicated customer was no longer at the indicated address, if this field is null then the customer is associated with the address)


Assuming also that you have a set of services that you could perform and that a customer can have many services at a particular address, I would recommend having a table to hold all services

tblServices
-pkServiceID primary key, autonumber
-txtService

Then you can relate the many services to the customer/address combination with another junction table

tblCustomerBusinessAddressServices
-pkCustBusAddServID primary key autonumber
-fkBusCustAddrID foreign key to
tblBusinessCustomerAddresses
-fkServiceID foreign key to tblServices

You could also use the same end effective date approach to handle when a customer stops a particular service at an address (while still keeping other services).

[mrjoshuaw]

Yes that is correct, we could have a new customer move into a location that would have had a previous customer as well. Which is what I am struggling with to be honest. Trying to get all of this data in, and since everyone wants to remember everything that has happend with that customer, or that property address, or the service address (yep they are different) is a pain. So I will look into the junction table, am I correct in assuming that is what the form would be pointed to for the data entry, as well as any querys I would need to run against the multiple tables?

Also, is there a best practice for amount of columns in a table? The reason I ask is that I have a customer table that has about 15 to 20 columns of info that pertains to that customer only. That specific data would not be found for any other customers at all. So would breaking the tables up the equivalent of "being redundant for redundant sake?" or in other words a waste of time?

[jzwp11]

am I correct in assuming that is what the form would be pointed to for the data entry

Typically you would use a main form with a subform. The main form would be based on table that makes up the one side of the one-to-many relationship while the subform is based on the table that makes up the many side of the relationship. With the many-to-many relationship the subform would be based on the junction table(tblBusinessCustomerAddresses)
while the main form could be based on the address table or the customer business table.

querys I would need to run against the multiple tables?

You would create a query that joins the three tables that make up the many-to-many relationship and then query that query.

Also, is there a best practice for amount of columns in a table?

No, it all depends on your data. I have found, however, that when people have tables with a lot of fields, it usually indicates that their database is not normalized. Basically you have to go through the fields and the data to be contained in each to determine whether all of those fields should be fields or actually records within a related table.

So what are the field names you have and what type of data do they hold?

[mrjoshuaw]

The Field Names are:

CustomerID
CustomerName
AccountStatus
MailingAddress
MailingAddress2
MailingCity
MailingSt
MailingZip
CustomerIP
GatewayIP - Customer Specific
Router1IP - Customer Specific
Router2IP - Customer Specific
Notes
InstallationApptDate
InstallarionApptTime
ServiceVerified
ContactFirst
ContactLast
ContactPhone
Contactemail
Website

[mrjoshuaw]

The rest of the customer info is in other tables, so for example their physical address or service address and the information that would tie to that physical location are all kept in the same table. The billing info (billing contact, billing address, account number, etc.) are kept in another table, and the property information (we have customers in a large building, and that building has a single address with multiple suites) has a seperate table. I tried to break the info down by physical properties (the things that could stay with the physical address even if the customer moves) and logical (if the customer DOES move these things will always be tied to that customer no matter what).

[jzwp11]

As described earlier you have a many-to-many relationship between the customer and their addresses (service) address, but you also have a billing address, a physical address. They are all addresses so they should all be in 1 table, they are just different types of addresses. So from where we left off previously:

tbleBusinessCustomer
-pkCustomerID primary key, autonumber
-txtCustomerName

tblAddresses
-pkAddressID primary key, autonumber
-txtAddress
-txtCity
etc.

tblBusinessCustomerAddresses
-pkBusCustAddrID primary key, autonumber
-fkCustomerID foreign key to tblBusinessCustomer
-fkAddressID foreign key to tblAddresses
-dteEndEffective (a date field to say when the indicated customer was no longer at the indicated address, if this field is null then the customer is associated with the address)
-fkAddressTypeID foreign key to tblAddressTypes

In the modified table above, I have added a field that will define the address type: service, billing, physical etc. I will just store these types in another table and use a foreign key to refer back

tblAddressTypes
-pkAddressTypeID primary key, autonumber
-txtAddressType

Now you can remove all of the address related fields from your customer table.

we have customers in a large building, and that building has a single address with multiple suites

You can define the suite in tblBusinessCustomerAddresses if the customer only occupies 1 suite, but if the customer has many suites at the particular address, that would describe a one-to-many relationship and the suites would have to go into a separate but related table (related to tblBusinessCustomerAddresses)

Looking at your other fields in the customer table, it looks like you have 2 IP fields just of different types. If a customer has more than 1 IP then that describes a one to many relationship, thus a separate table is needed. You'll need a field to distinguish the types of IP

The same can be said for the 2 router fields (one-to-many relationship)

Now regarding contacts, all people should be in one table. You can relate the person or persons to each address (or suite).

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblBusinessCustomerAddressesPeople
-pkBusCustAddPeopleID primary key, autonumber
-fkBusCustAddrID foreign key to tblBusinessCustomerAddresses
-fkPeopleID foreign key to tblPeople

Now a person can have many contact method types (e-mail, phone, fax etc.)

tblContactMethodTypes (holds records: phone, fax, e-mail etc.)
-pkContactMethodTypeID primary key, autonumber
-txtContactMethodType


Now relate the person to method type

tblPeopleContactMethods
-pkPeopleContactMethodID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkContactMethodTypeID foreign key to tblContactMethodTypes
-txtContactValue (the actual phone #, fax #, e-mail address etc.)

[mrjoshuaw]

jzwp11, thanks for the help and the direction! I really appreciate it and I will mark this post as answered.

[jzwp11]

You're welcome; good luck with your project.

[mrjoshuaw]

Ok, so I lied...I do have one more question. After creating all the tables that are needed, what I need to do is create a form for them to enter in all the data. The previous data base that they had used forms that were nested (I am assuming they were sub forms, just not labeled that way) and due to the relationships when you updated the form the related tables were updated as well.

With the Multiple tables do I need to base the form off of a query? The reason I ask is that I tried using the Form Wizard (I hate wizards) and when I added more then three tables it would not build the sub forms. So thus the conclusion on my end, that if I want multiple tables to be updated I will have to use a query to pull from each table and then use one form to use that query?

Thanks again for all the help, and let me know if I should move this to another topic!

[jzwp11]

The best approach is to use a form/subform design. Each form or subform would be based on a table (rather than a query). You might have to also navigate users through a series of form/subforms in order to populate all of the tables.

You can use the wizard to create each individual form and then go back to the main form (in design view) and drag the forms (from the navigation pane) that will be subforms into the main form. Typically, the main form is based on the table that makes up the one side of the relationship while the subform is based on the table that makes up the many side of the relationship.

For many-to-many relationships things are a little more complicated. For example, you might have a main form based on tbleBusinessCustomer
with a subform based on tblBusinessCustomerAddresses. You would use a combo box based on tblAddresses in the subform to populate the fkAddressID.

The trick comes in when you want to add a new address. You can do it in a couple of different ways.

First, have your users check to see if the address is already in tblAddresses, if so proceed to the main form. If not, navigate the user to a form to add the new address and then to the main form.

The second way is to use the On Not in list event of the combo box in the subform to open up another form to enter a new address. This will take a little VBA (Visual Basic for Application) code.