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 |
|
vermorel
Starting Member
26 Posts |
 Posted - 2006-05-11 : 09:09:22
|
| I would like to design a simple web forum with "tag" capabilities. I have some difficulties to actually translate the concept into an efficient database architecture; and I would like some advice on that matter.I would like to achieve a web forum where visitors can associates arbitrary "tags" (i.e. keywords) with any post + plus I would like to display a list "bar (17) - foo (3)" for a given post if this post has been tagged 17 times with "bar" and 3 times with "foo".Let's see a naive table design to do that:Table POSTS: POST_ID - USER_ID - POST_CONTENTTable TAG_DEFS: TAG_ID - TAG_NAMETable TAGS: TAG_ID - POST_ID - USER_IDSince I would like to display a list of posts (let's say the 20 most recent ones), I need a single query[*] that will let me retrieve all the necessessary information. I am not sure that the "naive" architecture here above is very efficient.Does anyone has a architecture (+ the associated query) to achieve such a tagged forum with good performance?Thanks in advance,Joannès[*] It's easy to actually perform the display by performing 1) a query to retrieve the POST_IDs 2) a serie of queries for each POST_ID. Yet this is very innefficient because of the large number of calls to the database. |
|
|
|
|
|
Database architecture for Tagged web Forums?