Best way to store and select multiple states/cities for a Company?

Question

Hello,

I have Company A. I need the ability to link this Company to multiple States, and each state can in turn be linked to multiple Cities. For example:

Company A
         \
          - State A
                  |-City A
                  |-City B
         \        |-City C
          - State B
                  |-City A
                  |-City B
         \        |-City C
          - State C
                  |-City A
                  |-City B
                  |-City C

My questions are:

1. What is the best way to store this in a table?
2. What is the easiest way to allow a user to choose this without multiple page refreshes?

I use ASP.NET (Framework 3.5) with C#. If there are any controls around that can do this, a pointer to them would be most appreciated too.

Thanks,

Jim

Edit: For example, I need to choose "Company A". Then choose "State A". Under "State A", I choose "City A, B & C". Then choose "State B" and under that choose "City A & B".

Answer 1

You will probably want to store this in four tables

**Companies**
Name OtherInfo CompanyID

**States**
Name OtherInfo StateID

**Cities**
Name StateID CityID OtherInfo

**CompaniesInCities**
CompanyID CityID

This way, states and cities can exist without companies.

As far as a control to select them, if you want to select a single city, an AJAX CascadingDropDownList may be the best option.

Doing this without multiple page refreshes, at least if I understand you correctly, will be extremely difficult and probably confusing for the user. Your best shot is to make them as painless as possible, which may or may not involve using AJAX.

Answer 2

To answer your first question, I would store it something like:

STATE
----------------------------
STATE_KEY                      NOT NULL NUMBER                                                                                                                                                                                        
COUNTRY                        NOT NULL VARCHAR2(3)    (ISO 3-letter code)                                                                                                                                                                               
STATE_SHORT                    NOT NULL VARCHAR2(30)                                                                                                                                                                                  
STATE_NAME                     NOT NULL VARCHAR2(30)  


CITY
----------------------------
CITY_KEY                       NOT NULL NUMBER                                                                                                                                                                                        
STATE                          NOT NULL NUMBER    (FK into STATE)                                                                                                                                                         
CITY_NAME                      NOT NULL VARCHAR2(30)                                                                                                                                                                                  
LAT                                     NUMBER                                                                                                                                                                                        
LOG                                     NUMBER  

COMPANIES
----------------------------
STATE_ID NUMBER NOT NULL      (FK into State)
.... Other Data Cols.....

One thing you might consider doing is to link the companies to the city key and FK into city instead, and have a cities like usa;fl;null and usa;ca;null to represent all cities within that state. This way converting over to companies based on a specific city doesn't really change your overall scheme. If you know that you'll always be dealing with companies linked to states, then doing the state_id in companies (as shown above) would be preferrable.

Good luck with your project.