When designing a database, think of it this way:
The "kinds of things" that you need to keep information about would map to
the first cut of what tables you need. So if you keep information about
Companies and Cities, you need (at least) the tables Companies and Cities.
Keep information (attributes) of companies in the Companies table, and
information about the cities in the Cities table. The attributes ("atomic"
information elements) gives you the first cut at what columns you need in
each table.
Establish simple (One to Many) relationships between the tables by creating
a column in the "related" table that contains a copy of the primary key in
the other table. For instance to represent that a Company is located in
(relationship: "located in") a certain City, add a column (for instance
called CityID) in the Companies table.
However, since some companies may exist in multiple cities, and multiple
companies exists in each city, you probably want to represent a Many to Many
relationship, which is implemented with an "intermediary" table - let's call
it CompanyCities - and give it two columns: copies of the primary keys from
Cities and Companies. This table gets populated with the combinations of
the values of the Cities PK and the Companies PK representing what companies
exist in which cities. For information that is specific to a company's
location/city, you would most likely put this in the CompanyCities table as
well.
Using these simple principles will help you well on the way to a third
normal form (3NF) database design.
So you'll have the following design (sample):
Cities:
CityID PK
CityName
County
Population
CityWebSite
...etc.
Companies:
CompanyID PK
CompanyName
CorporateHQ
CorporateWebSite
...etc.
CompanyCities:
CityID Part of PK - References Cities(CityID)
CompanyID Part of PK - References Companies(CompanyID)
Address
Phone
...etc.
To get a listing of Companies in a specific City, use a join something like
this:
SELECT CO.CompanyName,
CO.CorporateWebSite,
CC.Address,
C.CityWebSite
FROM Cities as C
INNER JOIN CompanyCities as CC
ON CC.CityID = C.CityID
INNER JOIN Companies as CO
ON CO.CompanyID = CC.CompanyID
WHERE C.CityName = 'London'
Hope this gives you something to work with.
For more information about normalization, check out
http://www.15seconds.com/issue/020522.htm
HTH,
Tore.
----- Original Message -----
From: "Andrew Maynes" <[EMAIL PROTECTED]>
To: "MySQL-Lista" <[EMAIL PROTECTED]>
Sent: Saturday, February 22, 2003 3:13 PM
Subject: Another Query Table Structure
> This may be simple for most of you MySQL experts out there so a little
help
> would be nice :)
>
> I have an index page that is using a Navigation system based on the ID
from a
> table City:
>
> The link then goes a DIR for that City.
>
> http://www.theaddress.co.uk/City/
>
> The navigation within this DIR is based on Traders for this City so my
problem
> is?
>
> Do I create a table for each City (holding Companies) or is there a way to
> structure the MySQL DB that can hold all companies in the same table?
>
> So when the City is selected all the companies for that City will be
listed,
> baring in mind that all the TraderID's are generic (the same as City)
It's only
> the Company that is specific to City?
>
> Help greatly needed and appreciated
>
> Andrew
>
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
