OT: well sort of - database design

This WebDNA talk-list message is from

2003

It keeps the original formatting. numero = 50323
interpreted = N
texte = Ok, hopefully this wont start too large a debate.Quickly here is the concept.A large community site (actually many community sites) with varioussections...Forums, classifieds, postcards, blogs, banners, etc. & profiles.Now there is a need to track everything a member does. Essentially in 2categories, viewing and participating. This is true of every section...Viewing forums / posting... Viewing banners / clicking...etc.I am currently dancing around the following relational concept:(all very high pass and lacking detail for this discussion)Section TableForums - Sku, title, message, date, etc.Classifieds - sku, title, category, etc.Banners - sku, client, totalviews, etc.Then a profile table:SKU, Name, email, etc.Now for the tracking part. I need a system that will accommodate yet to bedefined sections. In other words, we need a design that will allow us totrack new information WITHOUT recoding searches every time a new sectiongets added. So I am thinking about this:Connection TableSKU, TableREF, TREFsku, Taction, ProfileSKU, DateWhere tableref is a reference to a sku field in a sections table that listsall the 'sections' and associates them with a sku. This way we can add newsections for 'searching' via a single record in a tableref lookup.Where TREFsku is the actual record within the table mapped in the TABLEREF.This identifies a record of interest within the section table.Where Taction is one of a view choices (for now view or append)Where ProfileSKU is the sku field in the profiles table.Where Date is obvious.This lets us qualify matches such asShow me all the people that viewed discussions in the forums within thecategory of techsupport within the past month & who clicked banners within Xcategory.So we find the records in the forums table that fall under categorytechsupport and build a list of records (skus). Using those sku(s) we thenlookup in the connection system for records that match the TableRef field(forums) and also match the list or sku(s) array (threads under techsupport)that fall within a date range. From that we can extract a list ofProfileSKu(s). Based on sorting on ProfileSku we can also come up with adynamic variable for total accesses (most used) within that time frame.Ok, here is my concern. The connection table would create records (evensmall ones) for every view/action by every user. This system on just asingle community site currently getting 500,000 page views a month would belarge. Multiply that times a number of community sites all using the sameback end system (for cross demographic mining) and I am into a system thatcant handle its own girth.I have considered storing this information in a single profile record butthat leaves me with having to anticipate all sections in advance or recodingalong the way. It also creates just as many (or so it would seem) issueswith relational tables growing beyond practical sizes (profile would thenrelate to multiple threads (viewed) in the forums alone).I think that my relationships for the tracking table is sound, however I amconcerned about it growing beyond DNA (ram DB) far too quickly.Am I missing something obvious? Or is this simply going to force me into anenterprise system such as ORACLE?Anyone care to comment?ThanksAlexAlex J McCombie New World MediaChief Information Officer Drawer 607888/892.6379 Fair Haven, NY 13064Alex@NewWorldMedia.com http://OurClients.comInterface Designer WebDNA Programmer Database Designer-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ Associated Messages, from the most recent to the oldest:

OT: well sort of - database design (Alex McCombie 2003)

Ok, hopefully this wont start too large a debate.Quickly here is the concept.A large community site (actually many community sites) with varioussections...Forums, classifieds, postcards, blogs, banners, etc. & profiles.Now there is a need to track everything a member does. Essentially in 2categories, viewing and participating. This is true of every section...Viewing forums / posting... Viewing banners / clicking...etc.I am currently dancing around the following relational concept:(all very high pass and lacking detail for this discussion)Section TableForums - Sku, title, message, date, etc.Classifieds - sku, title, category, etc.Banners - sku, client, totalviews, etc.Then a profile table:SKU, Name, email, etc.Now for the tracking part. I need a system that will accommodate yet to bedefined sections. In other words, we need a design that will allow us totrack new information WITHOUT recoding searches every time a new sectiongets added. So I am thinking about this:Connection TableSKU, TableREF, TREFsku, Taction, ProfileSKU, DateWhere tableref is a reference to a sku field in a sections table that listsall the 'sections' and associates them with a sku. This way we can add newsections for 'searching' via a single record in a tableref lookup.Where TREFsku is the actual record within the table mapped in the TABLEREF.This identifies a record of interest within the section table.Where Taction is one of a view choices (for now view or append)Where ProfileSKU is the sku field in the profiles table.Where Date is obvious.This lets us qualify matches such asShow me all the people that viewed discussions in the forums within thecategory of techsupport within the past month & who clicked banners within Xcategory.So we find the records in the forums table that fall under categorytechsupport and build a list of records (skus). Using those sku(s) we thenlookup in the connection system for records that match the TableRef field(forums) and also match the list or sku(s) array (threads under techsupport)that fall within a date range. From that we can extract a list ofProfileSKu(s). Based on sorting on ProfileSku we can also come up with adynamic variable for total accesses (most used) within that time frame.Ok, here is my concern. The connection table would create records (evensmall ones) for every view/action by every user. This system on just asingle community site currently getting 500,000 page views a month would belarge. Multiply that times a number of community sites all using the sameback end system (for cross demographic mining) and I am into a system thatcant handle its own girth.I have considered storing this information in a single profile record butthat leaves me with having to anticipate all sections in advance or recodingalong the way. It also creates just as many (or so it would seem) issueswith relational tables growing beyond practical sizes (profile would thenrelate to multiple threads (viewed) in the forums alone).I think that my relationships for the tracking table is sound, however I amconcerned about it growing beyond DNA (ram DB) far too quickly.Am I missing something obvious? Or is this simply going to force me into anenterprise system such as ORACLE?Anyone care to comment?ThanksAlexAlex J McCombie New World MediaChief Information Officer Drawer 607888/892.6379 Fair Haven, NY 13064Alex@NewWorldMedia.com http://OurClients.comInterface Designer WebDNA Programmer Database Designer-------------------------------------------------------------This message is sent to you because you are subscribed to the mailing list .To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ Alex McCombie

DOWNLOAD WEBDNA NOW!

OT: well sort of - database design

2003

Top Articles:

Related Readings: