Designing a database

[slighltywhacked]

Hey all... I have been given the task of creating a database that will hold news articles. It has to hold the headline, body, date of submition, its id (aid) and the city of where it applies.

Most of it is pretty self forward. I can have all the values except the city one on one table. I need help to decide how Im going to make it so that an article can apply to many cities.
I dont want to put to values in one field.. lets say under city I have
city1, city2
if I do it this way I have to seprate them each time a particular city page calls for the articles. I do not want to have to hard code the different city names into the database because this script will be applied to many different cities, and I dont want to have to add collums to a table each time a new city is needed
ex.

article_body| Kelowna | Vernon | Vancouver |
fdsfdsa | NO | Yes | NO |

Doing it this way, everytime we move the script to a new site, when it calls this database and asks for article for city x, it will not find anything untill the table has a collum added for city x.

When the php script requests all the articles for a given city, it should be able to send the name of the city(that the script resides on) and pull anything that is marked as having the appropiate city.

I thought of doing the following:

article_body | city1 | city2 | city3 |
fdasfdsaf | Kelowna | Vancouver | none |

This would be okay, but first of all I would have many empty fields, and I would still have to add another cityx collum when another city website is created. It cannot be assumed that a given article can only have a maximum amount of cities (lets say 3). An article must have the ability to be in all the different cities. Im not sure if you are getting the picture of what Im saysing

Im thinking that their has to be at least two tables. One to hold all the information on the articles, and another table to hold all the cities its in or something like that... Honestly.. I have no clue how to do it... I hope someone else does

Thanx

[Theiggsta]

You should mirco-manage all yoru database data to save it from slowing down the database tremendously. But you should make general categories for everything in your database.

Remember to add index's to speed up the databse lookups, be sure to check out the vbulletin.com forums for this by doing a search for adding index's.

I think you should make an entire table devoted to cities and have a cityID for each one. Then for the article area, make a cityID add to each one. Its not micro managements, but there are ways to do it if you put your mind to it.

[slighltywhacked]

Micro Management?

what do you mean by general catagories?

I could do that, but I need to have more than one cityid per article

[Theiggsta]

Then when inserting the city IDs add in commas, and when reading them, do an explode() and shove em into an array, thats easy to do.

Micromanagement basically means you break it down to smaller pieces so its easier to do it...but General Categories is the easier way to go about a databae, that way there isint a whole lot of database slowdown in a bunch of smaller queries.

[Skunk]

What you are talking about is a many to many relationship. This is accomplished using a lookup table - here's an example:

news table
==========
news_id
headline
body
date_added
aid

cities table
============
city_id
city_name

news_cities table
=================
news_id
city_id

The news table stores news articles, the cities table stores cities and the news_cities stores information on which articles belong to which cities. For example, an entry in the news_cities table with news_id set to "3" and city_id set to "5" would mean that news article number 3 is related to city number 5. Articles can be in as few or as many cities as you like.

To get information back out of the table you use select joins. The followign query will return all of the news stories applicable to a city with ID number $id:

$result = mysql_query("SELECT news.*, cities.city_name
               FROM news, cities
               WHERE news.news_id = news_cities.news_id
               AND cities.city_id = news_cities.city_id
               AND news_cities.city_id = '$id' "); 

Using a lookup table like this is standard practise for SQL database design. For more information, a good tutorial on database design is available here:

http://www.webmasterbase.com/article.php/378

[slighltywhacked]

Skunk, thats kind of what I was thinking of using, but I dont see how an article can be related to more ciites?

[RKuhle]

From what you were saying it sounded like you needed a solution for several cities per article? Anyway, sure, several cities can easily be affected by one article. Say the Chicago Cubs sweep the St. Louis Cardinals next week (sweet!), residents of both Chicago and St. Louis would love to hear about this. Not to mention anyone running for a playoff placement (for later in the season).

If you have MySQL by Paul DuBois (sp?) you may want to thumb through the opening chapters. He really covers the multiple table strategy well using an example program he created specifically for the book.

[slighltywhacked]

yeah.. u got the right idea.. but I dont have that book

[slighltywhacked]

I have decided that in the article table I will have a cid (city id) field that will store numerical values that coorensponds with a city from the city table. Can someone tell me how I can get multiple values in that 1 field. Then suck them using an array and then checking that array with the city table to see what city(s) should display that article. Im guessing in the cid field in the article table it should be something like
1,3,5
where 1 3 and 5 coorespond with a particular cid(city id)

[slighltywhacked]

One table I have made has to collums

| cid | aid |


both are foriegn primary keys.. in this table do I make one or the other a primary key, or do I make them both (how?)

Also, neither the aid or cid collum can be unique, they will both have same values more than once
thanx