×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL extreme newbie question

SQL extreme newbie question

SQL extreme newbie question

(OP)
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.
Thanks
Perry Lowe

RE: SQL extreme newbie question

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 ;-)

RE: SQL extreme newbie question

(OP)
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).

Thanks for your help

RE: SQL extreme newbie question

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?

RE: SQL extreme newbie question

(OP)
Exactly!
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.
Thanks

RE: SQL extreme newbie question

Why not have two tables like this:

table Sports(
SportCode integer(5),
SportName string(50)
);

table Headlines(
HeadlineDate date,
SportCode integer(5), -- linked to Sports.SportCode
HeadlineText string(255),
AnOtherField string(20)
)

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:

select HeadlineDate, SportName, HeadlineText
from Headlines, Sports
where Headlines.SportCode = Sports.SportCode
and (any other selection criteria)
order by HeadlineDate;

-ml

RE: SQL extreme newbie question

(OP)
Thanks Mike,
I think that will work.
That would also give me some room to work if I want to later add an email table or other feature.
One other question.
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).

Thanks again

RE: SQL extreme newbie question

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.

table HeadLineData(
HeadLineCode integer(5),
HeadLineSequence integer(5),
HeadLineText string(10) -- or the max string length
)

Then store the HeadLineCode in your HeadLines table instead of the HeadLineText column. To store headline text you do this:

you store "THIS IS A HEADLINE STORED OVER MORE THAN ONE RECORD" like this

HeadLineCode, HeadLineSequence, HeadLineText
------------- ----------------- ------------
1 1 THIS IS A
1 2 HEADLINE S
1 3 TORED OVER
1 4 MORE THAN
1 5 ONE RECORD

You retrieve the headline by saying:

select HeadLineText
from HeadLineData
where HeadLineCode=1
order by HeadLineSequence

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.

RE: SQL extreme newbie question

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!

When you get more time, pick up a book on data modeling / database design. Look up "data normalization."

RE: SQL extreme newbie question

>Defining separate tables for each sport goes against the >whole concept of why we use databases to begin with!

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.

RE: SQL extreme newbie question

I apologize for the tone of my comment regarding "the whole concept of why we use databases."

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?

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.

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...

Each ActivityType could have multiple Activities. Each Activity could have multiple Headlines.

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 :)

RE: SQL extreme newbie question

(OP)
Thanks for everyones comments.
I think I am getting a good handle on
the database concept now from all your
help and all the reading I've been doing.

Thanks,
Perry

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close