Relational Database & CFM Issues

Question

Issue: I currently have a table in a database that is set up
as such:

1st Column Lists Company Names Following columns lists an a
short hand edition of the type of business each company does in
each state. i.e. In Illnois Comany A provide Type Q Services in
Arkansas Type R etc..

Based on a user selection of a particular state how can I get
the list of CompanyNames and only the selected states information
to display on the page?

CompanyName I IL I AR I AL I CO I
Company A Q R R L
Company B L R Q Q
Company C R R L L
Company D Q L Q Q

Thanks,

P

Amiable_Penguin15D5 · Answer

The database layout has to be updated to do what you want done.

Rules of Data Normalization

Table: Companies
--------------------------
CompanyID [ integer ] [primary key] (auto)
CompanyName

Table: BusinessTypes
-------------------------------
BusinessTypeID [ integer ] [primary key] (auto)
BusinessType [ varchar ]

Table: CompanyBusinessTypes
-----------------------------------------------
CompanyID [ Foreign Key: Companies.CompanyID ]
State [ char(2) ]
BusinessTypeID [ Foreign Key: BusinessTypes.BusinessTypeID ]

This layout allows you to scale up to infinite associations between companies, states and business types.

SELECT CompanyID, BusinessTypeID FROM CompanyBusinessTypes WHERE State = '#state#'

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded