SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Designing a database

    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

  2. #2
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Micro Management?

    what do you mean by general catagories?

    I could do that, but I need to have more than one cityid per article
    Last edited by slighltywhacked; May 30, 2001 at 22:11.

  4. #4
    Talk to the /dev/null Theiggsta's Avatar
    Join Date
    Mar 2001
    Location
    Tampa, FL
    Posts
    376
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Aaron "Theiggsta" Kalin
    Pixel Martini
    Ruby and Rails Developer

  5. #5
    Grumpy Mole Man Skunk's Avatar
    Join Date
    Jan 2001
    Location
    Lawrence, Kansas
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you are talking about is a many to many relationship. This is accomplished using a lookup table - here's an example:
    Code:
    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:
    PHP Code:
    $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

  6. #6
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Skunk, thats kind of what I was thinking of using, but I dont see how an article can be related to more ciites?

  7. #7
    What's HTML?
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    1,701
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Ryan Kuhle - A Proud Advisor - Got Questions? Just Ask!
    Get your website started for less than $20! Click Here

  8. #8
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah.. u got the right idea.. but I dont have that book

  9. #9
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  10. #10
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Last edited by slighltywhacked; Jun 1, 2001 at 10:09.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •