On Wed, 17 May 2000 21:24:22 -0400, "Jeff Fongemie" <[EMAIL PROTECTED]> wrote:
>
> Hey everyone,
>
> I need to make a dealer locator for a company. Just a drop down list to
> select state then a list.
> Do I need a seperate table for each state, with each table having same
> columns: dealer_name, dealer_address, dealer_telnumber etc...
> This means making 52 tables! If I need to make a change I will need to
> change each table!
> Is there a better way??
Yes. Think about the entities you have - dealers and states, every dealer is in a
state, a state may have zero or more dealers - that is your relationship, I'd draw an
E-R schema but ascii isn't good at that, so here is the relational schema...
STATES
[ ID | NAME ]
pk
DEALERS
[ ID | NAME | ADDRESS | ... | STATELINK ]
pk fk
where your states table will have a record for each state, your dealers table will
have a record for each dealer and each dealer record will contain in STATELINK the ID
of a state (STATELINK is a foreign key forming a relationship between DEALERS and
STATES).
So that...
SELECT Dealers.Name AS DealerName, States.Name AS StateName
FROM Dealers, States
WHERE Dealers.Statelink = States.ID
ORDER BY StateName;
will give you a query of dealers associated with thier state in order of state.
Study some principles of Realtional Database Design.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
