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 with MySQL). 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

 

[Guest_imported]

I can help you if I could only understand a little better what you are doing. What data would these tables consist of? are we talking scores/home-away stats/player-stats and all that, or just headlines from games, or what? Eithor way it sounds like you would be best off to go with seperate tables for each sport, and use a unique identifier in that tables to join them to each other... if you give me more details, I will surely be able to give you more ;-)

 

[lowtide]

Thanks for the reply. After doing some more reading (which is all I feel I've been doing for the last month) and from what you said in your post I think I'll go with a table for each sport. The only thing I'm not sure of is how to join all of the sports (13 in all) tables together to do a query for which headlines are eligable for the mainpage of the site. Each individual sport has it's own headline page and a flagged (yes/no) field in the database gives a headline the eligability to appear on the mainpage (only for the most important ones). <br>
<br>
Thanks for your help

 

[Guest_imported]

I see, so the editor (or someone in that capacity) would go through a, check which headlines are to appear, and you want to do a look-up of each sport, grabbing only those headlines that have been checked?

 

[lowtide]

Exactly!<br>
Now my question is how to do the multiple joins between all 13 sport tables. I am using MySQL which does not support views, which if I'm not mistaken would be the way to go. Perhaps at this point it would be a better question for the MySQL forum. I'll post there also.<br>
Thanks

 

[MikeLacey]

Why not have two tables like this:<br>
<br>
table Sports(<br>
SportCode integer(5),<br>
SportName string(50)<br>
);<br>
<br>
table Headlines(<br>
HeadlineDate date,<br>
SportCode integer(5), -- linked to Sports.SportCode<br>
HeadlineText string(255),<br>
AnOtherField string(20)<br>
)<br>
<br>
This get's rid of the need to have a table for each sport and let's you add sports easily. You can join the tables in a query like this:<br>
<br>
select HeadlineDate, SportName, HeadlineText<br>
from Headlines, Sports<br>
where Headlines.SportCode = Sports.SportCode<br>
and (any other selection criteria)<br>
order by HeadlineDate;<br>
<br>
-ml<br>
<br>

 

[lowtide]

Thanks Mike,<br>
I think that will work.<br>
That would also give me some room to work if I want to later add an email table or other feature.<br>
One other question.<br>
For the headline text field what would be the best datatype definition to use. Some of the stories get pretty big (well over 255 characters).<br>
<br>
Thanks again

 

[MikeLacey]

That would depend upon the maximum field length of a string field in your database. If that's not long enough - store your headline data in a table that looks like this.<br>
<br>
table HeadLineData(<br>
HeadLineCode integer(5),<br>
HeadLineSequence integer(5),<br>
HeadLineText string(10) -- or the max string length<br>
)<br>
<br>
Then store the HeadLineCode in your HeadLines table instead of the HeadLineText column. To store headline text you do this:<br>
<br>
you store "THIS IS A HEADLINE STORED OVER MORE THAN ONE RECORD" like this<br>
<br>
HeadLineCode, HeadLineSequence, HeadLineText<br>
------------- ----------------- ------------<br>
1 1 THIS IS A <br>
1 2 HEADLINE S<br>
1 3 TORED OVER<br>
1 4 MORE THAN <br>
1 5 ONE RECORD<br>
<br>
You retrieve the headline by saying:<br>
<br>
select HeadLineText<br>
from HeadLineData<br>
where HeadLineCode=1<br>
order by HeadLineSequence<br>
<br>
As you loop through the rows you get back you stick all the values you get from HeadLineText into one variable - and there's your headline. Not as simple as storing it in one row and column but it does remove any length limits.<br>

 

[bitbrain]

YES YES YES - go with the Headline & Sport (always use singular with table names) tables. Defining separate tables for each sport goes against the whole concept of why we use databases to begin with! <br>
<br>
When you get more time, pick up a book on data modeling / database design. Look up "data normalization."<br>
<br>

 

[Guest_imported]

&gt;Defining separate tables for each sport goes against the &gt;whole concept of why we use databases to begin with! <br>
<br>
I thought the whole concept of why we use databases to begin with would be to store data in multiple tables, using relational data models and command syntaxes to retrieve and manipulate the data... hmmm guess I have more learning to do.

 

[bitbrain]

I apologize for the tone of my comment regarding "the whole concept of why we use databases."<br>
<br>
One of your design goals should be to minimize the ongoing maintenance effort of the system. You don't want to define a new table and update your queries each time a new sport is added. Also, the queries will be simpler if they go against a single table vs. 13. The beauty of a well-designed database is that it is modeled for the future and is adaptable to unexpected changes. Who knows what sports the college will have five years from now?<br>
<br>
Creating a single table for each sport is a very literal approach to solving the problem. From a design perspective, creating a single table called "Sport" brings us up a level of abstraction. The abstract solution is usually preferable.<br>
<br>
To carry the concept one step further... Instead of a table called "Sport," you could create a table called "Activity." Then, the system could be used for non-sport news such as the Arts etc. In this scenario you would also need an "ActivityType" table that would contain rows for Sport, Music, Speech... <br>
<br>
Each ActivityType could have multiple Activities. Each Activity could have multiple Headlines. <br>
<br>
By making a simple design change you broaden the potential scope of the system from the sports department to the whole college! - Don’t forget to adjust your fee accordingly <br>
<br>

 

[lowtide]

Thanks for everyones comments.<br>
I think I am getting a good handle on<br>
the database concept now from all your<br>
help and all the reading I've been doing.<br>
<br>
Thanks,<br>
Perry