One customer, many addresses, many services

[frank929rr]

I have "Customer" table linked to "Address" by CustID.  No Problem there.  Each customer can have multiple addresses with multiple services.

 

I'm trying to figure out a way to link the "Services" table to the "address" table but here is the issue.

 

I want all the available "services" to show up in a portal in the "address" layout.  I've linked them by using a "address_lineitem" table, since the price is different for each "customer"

 

Is there a way to have that portal of all available services dynamically update if we add a new service or change the name of a service in the "services" table?  

 

Do I have to script it or is there an easier way?  I feel like there should be an easier way, but I'm just not seeing it.

 

Thanks.

 

 

[Kris M]

You need to figure out the interactions and relationships between the entities in your system first.

Start with basic Entity Relationship modeling... if you have not already done so.

http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model

Sounds like you have Customers, Addresses, and Services

Does a customer have one address or many address?

Does a customer purchase services?

if yes then can the customer purchase the same service multiple times?

How does an address relate to a service?

You'll probably end up with a few join tables to knit things together efficiently.

Build the foundation (database) before the roof (UI).

[comment]

Assuming you have:

 

Customers -< Addresses -< AddressServices >- Services

 

place a portal to AddressServices on a layout of Addresses, and show the Services::Name in the portal (along with fields from AddressServices, such as price).