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.

 

 

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).

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).