| Author |
Topic |
|
KenA
Starting Member
28 Posts |
 Posted - 2003-12-17 : 13:09:22
|
| I have one question that maybe someone who already developed one News System may have a tip for me. Usually when we see web sites displaying a News block, it usually does so by first showing a snippet of the whole topic with a link like 'more' or 'details'. The doubt I have is whether the content for this snippet comes from the whole content from the NewsContentTable or from another table, let´s call SnippetTable that stores just part of the whole NewsContentTable?Which approach would be better to take?Regards,»»» Ken.Awamura |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-17 : 13:31:19
|
| For performance sake, it would be better to have the snippets in a different table. If you aren't considering performance, I would still go with a snippet table, which would have the same primary key as the contents table and just one other column.Tara |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-12-17 : 13:45:40
|
| Sounds like a content management system I worked with...the entire pages were rendered from the databaseheaders, nav bars, snippets...Tara why do you think it should be in a sepearte table?I'd just keep it with the article...in a separate column...That way it can be modified...Brett8-) |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-12-17 : 15:12:53
|
| I have done this for a news site. I had a seperate table with the same news story ID as the index and a varchar column for the snippet. The reason for a seperate table is it was a lot quicker to query when just composing an index page of stories.I used Insert and Update triggers on the main table to update my snippet table whenever a story was inserted or added. It can look a bit more professional if you don't just chop it off mid word - search for the next space after a set number of characters. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-17 : 15:28:43
|
| Brett, Well mostly because of performance. And even if you start off small and performance is not a problem, it'd be better for a page that has to grab lots of snippets of information to get the data from a narrow table than to get it from a wider table. But that's just my $0.02.Tara |
|
|
|
KenA
Starting Member
28 Posts |
Posted - 2003-12-17 : 15:28:52
|
| Thanks all ... I never did a News System but my feeling was that a SnippetTable or at least a SnippetCollumn would be great. Regarding how I would create the snippet I´ll probably go with 'darinh' explanation, eg : use String.Substring() and read a few full words before chopping the full content to create the snippet.»»» Ken.Awamura |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-17 : 18:13:39
|
If your table was :Create Table News(newsID int NOT NULL PRIMARY KEY IDENTITY, --for Rob  newsHeadline varchar(300),newsDate datetime,newsSnippet varchar(500),newsBody Text)and you just did a :SELECT newsID, newsHeadline, newsDate, newsSnippetFROM newssurely that would be just as fast as storing the body in a separate table. Text columns aren't stored in the same place so your actual table would still be fairly narrow.Damian |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-17 : 18:17:19
|
quote:
Originally posted by Merkin
Text columns aren't stored in the same place so your actual table would still be fairly narrow.
True. I didn't think of that.Tara |
|
|
|
Granick
Starting Member
46 Posts |
Posted - 2003-12-17 : 19:06:17
|
| I once built a site that did something very similar, all sports data. The data came from one of the major sports info centers, and I had to parse it all, stories, scores and such. I used a text field, after learning a painful lesson on the problems of using a varchar for this :). I would just grab the first x number of characters of the story, and then use code to "intelligently" break it down so it didn't cut off in the middle of a word, etc. I wish I still had access to all that SQL sometimes as a reminder, mostly as what NOT to do.Something else I learned the very hard way on that project. Constantly refreshing the topics like that from a Database is great, until are processing so many requests that it would take over 30 seconds for a simple request from the News table, which usually takes well less than a second. I had to convert over to a setup where I built an XML file whenever a news piece was updated/inserted/deleted, etc. That way, I could let the webservers do what they are good at, and it was only needing to be rebuilt in very controlled situations. Although I also found out that it is best to have a tool to do a "manual re-build" too.Just something to think about as you are planning your strategy.Shannon |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-12-18 : 01:45:33
|
| Shannon, you probably dont need to go through all that trouble now if you use ASP.NET - the caching options are simply fantastic!And I'd go with Merkin's idea of having a Snippet column in the same table instead of a dedicated Snippet table, as long as you name the columns you need and don't select the NewsBody column, things should be fast enough.OwaisWe make a living out of what we get, but we make a life out of what we give. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-18 : 08:12:28
|
You can do caching in classic ASP. You just have to write code for it  Damian |
|
|
|
|
Designing a News System question