Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2009-01-11 : 13:37:55
|
| Hello -I am beginning the task of designing a content database that will be used to store many types of content/media. It will be used, for example, to store: Word Docs, PDFs, Small bits of video, PowerPoint presentation, Images.The idea is that we'll have a interface that allows users to search this content database...using keywords and then be presented with a list of matches.With the matches...we want to display some general attributes from the content - Title, date published, etc.However, there also needs to be a way to display: An abstract, maybe the first page of documents and then the final full document or image or video/podcast.Because not all rows in the Content table will be the same format/media...my current design has the things like Abstract and FullContent in separate tables, related by an internal reference ID #. Here is a simplified view of the db model: ContentLibrary ContentReferenceID - int...but might change to GUID ContentTitle - nvarchar ContentPublishDate - smalldatetime Content_Abstract ContentReferenceID -foreign key to ID from Content Lib table ContentAbstract - nvarchar(max) Content_Full ContentReferenceID - foreign key to ID from Content Lib table Content - varbinary(max)Here is my question: Does this seem like a good way to design this? I think that I'll need to work with Full Text Search too. This is in SQL 2005. I was thinking that having the larger bits of data - abstract, full document/media - in a separate table would help with indexing, performance, etc. But not sure as I have never built something exactly like this before.Any guidance or suggestions will be appreciated.thanks - will |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-01-12 : 07:15:16
|
| Looks ok to me assuming your data types work for you. |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-01-12 : 13:18:14
|
| Thanks for the review and feedback. Yes, the data types do work for me.I was also reading more about Sql's full-text catalogs/indexes this weekend and being able to store the documents (doc, pdf, ppt, etc) within the table for sql's full-text search to parse and find keywords is fantastic.I read some examples where there were two tables (similar to my approach) with the documents stored in a separate table, and another example with a single table containing all the attributes along with the native document. Not sure which approach is better. |
|
|
|
|
|
|
|
|
Content database - storing abstracts and full doc