SQL extreme newbie question

[lowtide]

I know this is very basic but I am new to database programming and under pressure to create a database for a client within the next month and make it efficient. I understand how to create the database and how to access it dynamically (I will be using PHP). I just want to make sure the initial setup is the best way to do it. What I have is a headline and news database for a college sports department. I want to be able to query the database for the information that will be used in for each sport and also a combined query to show headlines of the top stories on the main page. Would it be better to create one big table with a field for the name of the sport or seperate tables for each sport. If I create seperate tables how do use the join function to query more than two of the tables to find the latest 5 headlines for all of the sports to be displayed on the mainpage. Sorry for the length of this post, it's the simplest explaination I could come up with.<br>
Thanks<br>
Perry Lowe

 

[cfhomer]

I don't know if this reply is coming too late Perry, but I would suggest you have the one big table. That way it is very easy to show combined headlines from all the sports by simply ignoring the sport field, and you can also have specific sport pages that only show their own headlines.

 

[aura9]

Although it is always easier to create one big table, I think that it does not allow for scalability. However the answer isn't to put all of the sports in their individual fields either.

Try this:

STORIES Table
------------------------------------------------------
| key_id |  date  |  headline  |  body  |  sport_id  |
------------------------------------------------------
| 1      |2000/4/1|The Rams Win| blah bl|  1         |
------------------------------------------------------
| 2      |2001/5/4|Yankees Head| blah bl|  4         |
------------------------------------------------------

SPORTS Table

------------------------
| sport_id |   sport   |
------------------------
| 1        | football  |
------------------------
| 2        | Voleyball |
------------------------
| 3        | Lacrosse  |
------------------------
| 4        | Baseball  |
------------------------


Then if you need to retrieve the first story and display it...

SELECT * FROM sports, stories 
WHERE sports.sport_id=stories.sport_id AND
stories.key_id='1';

Hope this helps. There really are five million ways to do things and depending on your situation one way might be better then another. Keep your options open.

Good Luck!