Re: well sort of - database design

This WebDNA talk-list message is from

2003

It keeps the original formatting. numero = 50324
interpreted = N
texte = Well the thing here is that if your appending to the databases on each useraction, i don't think it matters soo much how big the tables get... it juststicks the row at the end of the table and goes on its marry way.its just when it comes time to running your queries that you would haveissues.but thats about when you flush the databases, take a copy of them and putthem on your development server to run the queries... that way not nailingyour production server with the big loopey stuff.this worked fine for me with database files upwards of 60mb... just need theram baby! appends didn't suffer at all with the biggie sized db's----- Original Message -----From: Alex McCombie To: WebDNA Talk Sent: Tuesday, May 13, 2003 1:43 PMSubject: OT: well sort of - database design> Ok, hopefully this wont start too large a debate.> Quickly here is the concept.> A large community site (actually many community sites) with various> sections...> Forums, classifieds, postcards, blogs, banners, etc. & profiles.> Now there is a need to track everything a member does. Essentially in 2> categories, 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 Table> Forums - 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 be> defined sections. In other words, we need a design that will allow us to> track new information WITHOUT recoding searches every time a new section> gets added. So I am thinking about this:>> Connection Table> SKU, TableREF, TREFsku, Taction, ProfileSKU, Date>> Where tableref is a reference to a sku field in a sections table thatlists> all the 'sections' and associates them with a sku. This way we can add new> sections for 'searching' via a single record in a tableref lookup.>> Where TREFsku is the actual record within the table mapped in theTABLEREF.> 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 as>> Show me all the people that viewed discussions in the forums within the> category of techsupport within the past month & who clicked banners withinX> category.>> So we find the records in the forums table that fall under category> techsupport and build a list of records (skus). Using those sku(s) we then> lookup in the connection system for records that match the TableRef field> (forums) and also match the list or sku(s) array (threads undertechsupport)> that fall within a date range. From that we can extract a list of> ProfileSKu(s). Based on sorting on ProfileSku we can also come up with a> dynamic variable for total accesses (most used) within that time frame.>>> Ok, here is my concern. The connection table would create records (even> small ones) for every view/action by every user. This system on just a> single community site currently getting 500,000 page views a month wouldbe> large. Multiply that times a number of community sites all using the same> back end system (for cross demographic mining) and I am into a system that> cant handle its own girth.>>> I have considered storing this information in a single profile record but> that leaves me with having to anticipate all sections in advance orrecoding> along the way. It also creates just as many (or so it would seem) issues> with relational tables growing beyond practical sizes (profile would then> relate to multiple threads (viewed) in the forums alone).>> I think that my relationships for the tracking table is sound, however Iam> concerned about it growing beyond DNA (ram DB) far too quickly.>> Am I missing something obvious? Or is this simply going to force me intoan> enterprise system such as ORACLE?>> Anyone care to comment?> Thanks> Alex>> Alex J McCombie New World Media> Chief Information Officer Drawer 607> 888/892.6379 Fair Haven, NY 13064> Alex@NewWorldMedia.com http://OurClients.com>> Interface 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/-------------------------------------------------------------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:

Re: well sort of - database design (Alex McCombie 2003)
Re: well sort of - database design (Kenneth Grome 2003)
Re: well sort of - database design (Alex McCombie 2003)
Re: well sort of - database design (Kenneth Grome 2003)
Re: well sort of - database design (Andrew Simpson 2003)
Re: well sort of - database design (Alex McCombie 2003)
Re: well sort of - database design (Andrew Simpson 2003)
OT: well sort of - database design (Alex McCombie 2003)

Well the thing here is that if your appending to the databases on each useraction, i don't think it matters soo much how big the tables get... it juststicks the row at the end of the table and goes on its marry way.its just when it comes time to running your queries that you would haveissues.but thats about when you flush the databases, take a copy of them and putthem on your development server to run the queries... that way not nailingyour production server with the big loopey stuff.this worked fine for me with database files upwards of 60mb... just need theram baby! appends didn't suffer at all with the biggie sized db's----- Original Message -----From: Alex McCombie To: WebDNA Talk Sent: Tuesday, May 13, 2003 1:43 PMSubject: OT: well sort of - database design> Ok, hopefully this wont start too large a debate.> Quickly here is the concept.> A large community site (actually many community sites) with various> sections...> Forums, classifieds, postcards, blogs, banners, etc. & profiles.> Now there is a need to track everything a member does. Essentially in 2> categories, 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 Table> Forums - 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 be> defined sections. In other words, we need a design that will allow us to> track new information WITHOUT recoding searches every time a new section> gets added. So I am thinking about this:>> Connection Table> SKU, TableREF, TREFsku, Taction, ProfileSKU, Date>> Where tableref is a reference to a sku field in a sections table thatlists> all the 'sections' and associates them with a sku. This way we can add new> sections for 'searching' via a single record in a tableref lookup.>> Where TREFsku is the actual record within the table mapped in theTABLEREF.> 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 as>> Show me all the people that viewed discussions in the forums within the> category of techsupport within the past month & who clicked banners withinX> category.>> So we find the records in the forums table that fall under category> techsupport and build a list of records (skus). Using those sku(s) we then> lookup in the connection system for records that match the TableRef field> (forums) and also match the list or sku(s) array (threads undertechsupport)> that fall within a date range. From that we can extract a list of> ProfileSKu(s). Based on sorting on ProfileSku we can also come up with a> dynamic variable for total accesses (most used) within that time frame.>>> Ok, here is my concern. The connection table would create records (even> small ones) for every view/action by every user. This system on just a> single community site currently getting 500,000 page views a month wouldbe> large. Multiply that times a number of community sites all using the same> back end system (for cross demographic mining) and I am into a system that> cant handle its own girth.>>> I have considered storing this information in a single profile record but> that leaves me with having to anticipate all sections in advance orrecoding> along the way. It also creates just as many (or so it would seem) issues> with relational tables growing beyond practical sizes (profile would then> relate to multiple threads (viewed) in the forums alone).>> I think that my relationships for the tracking table is sound, however Iam> concerned about it growing beyond DNA (ram DB) far too quickly.>> Am I missing something obvious? Or is this simply going to force me intoan> enterprise system such as ORACLE?>> Anyone care to comment?> Thanks> Alex>> Alex J McCombie New World Media> Chief Information Officer Drawer 607> 888/892.6379 Fair Haven, NY 13064> Alex@NewWorldMedia.com http://OurClients.com>> Interface 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/-------------------------------------------------------------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/ Andrew Simpson

DOWNLOAD WEBDNA NOW!

Re: well sort of - database design

2003

Top Articles:

Related Readings: