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
Thanks
Perry Lowe
RE: SQL extreme newbie question
RE: SQL extreme newbie question
Thanks for your help
RE: SQL extreme newbie question
RE: SQL extreme newbie question
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
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
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
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
When you get more time, pick up a book on data modeling / database design. Look up "data normalization."
RE: SQL extreme newbie question
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
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
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