SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
Thread: Designing a database
-
May 30, 2001, 18:25 #1
- 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
-
May 30, 2001, 18:51 #2
- 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.
-
May 30, 2001, 21:12 #3
- 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 articleLast edited by slighltywhacked; May 30, 2001 at 22:11.
-
May 30, 2001, 23:16 #4
- 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.
-
May 30, 2001, 23:55 #5
- 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
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' ");
http://www.webmasterbase.com/article.php/378
-
May 31, 2001, 09:12 #6
- 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?
-
May 31, 2001, 09:48 #7
- 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
-
May 31, 2001, 16:11 #8
- 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
-
May 31, 2001, 17:39 #9
- 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)
-
Jun 1, 2001, 10:06 #10
- 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
thanxLast edited by slighltywhacked; Jun 1, 2001 at 10:09.
Bookmarks