Hi Guys, I've been working on a project for quite sometime, and one of my core features is essentially finished. While I haven't stress tested it it seems to work great. Along the way, I've had smaller questions and I've had them resolved through this community. Now that it's all working, I want to look back and make sure I'm doing things the best way.
I wrote up an outline of how it all works, and I'm hoping some of you can take a look and see if anything design flaws jump out at you. If I'm going to change anything, now is the time. If I can't find any major issues, I'm going to push this out for a small beta trial. Can you guys/gals take a quick read through this? It's pretty straightforward and self explanatory. s Thanks! Once I get this finished, I'll be happy to post my source code if anybody likes this function. *URL Redirects * *Overview* The goal with this function is to provide users with a url shortening service (aka tinyURL and Bit.ly). Users will be able to create shortened urls that point to other websites, but the shortened url's will also be used for other features in the app meaning the redirect function design needs to inclusive of the needs of these other features. *How it works* When 404 error thrown (resource not found), a function will strip down the requested URL to get the subfolder then query that subfolder against a database. If no match found, throw 404 error. 1) User requests company.com/mydemopage 2) /mydemopage is not a real directory so server throws 404 error 3) 404 error script will first run the url parsing script to strip out the relevant subfolder 'mydemopage' 4) Script will then query redirect database for 'mydemopage' and if there is a match, forward user to the URL specified in the database. If there is no match, present 404 Error page. *Functional Considerations* * Along with making specific redirects, users can also use the site for other reasons, such as to host images, places classified ads, make custom web-pages, etc. These features may also create a redirect, such as 'details.at/mynewpage' will point to content created by the user. * Anonymous/non custom url's (shortened url's with randomly generated subfolders) will be re-used unless the original creator specifies otherwise. What this means, is that if someone submits a long url that has been used before, they will be given the same shortened URL as the last user. If the creator doesn't want their redirect reused, they must flag the redirect as 'unique' prior to it being reused. *If a redirect is re-used, it can no longer be flagged as unique, and is instead flagged as 'locked' and can no longer be edited. *Redirects can be edited as long as they are assigned to a specific user and not locked. *Database Structure* There are several tables involved; a core table, ownership table, hit detail table, and a hit count table. Table 1) redirects - This is the primary table that contains the core data needed for the redirect system. For this application, most, if not all of this data is needed anytime a forward is initiated. In the context of this app, there isn't any performance advantage to breaking these tables into smaller units, adding more uniqueID's, etc. Table 2) redirectOwners - Since multiple users can all own a single redirect, a one-to-many relationship exists, and a seperate table is needed. Table 3) Hits - Everytime a redirect is utilized, a 'hit' is logged and details about the hit (referrer, browser, time, etc) are logged to this table. **location is compiled when the record is created by querying a table of geographical locations based on IP. If recompiled at a later date, the IP location information may change and misrepresent the original location. This is why I keep the location in the table and do not compile it based on IP every single time the hit details are viewed. * **The referrer host and string are kept seperating so that visits can be grouped by host, not just the overall url. For instance, if a redirect is posted on 20 different places in facebook, stats will show all the hits from facebook, plus each specific hit from the various locations on facebook. * Table 4) hitCounter - To keep an accurate and current count of hits to a particular redirect, a table that logs each new hit as a +1 to the previous will be used in conjunction with the existing 'hits' table. This table will simply icrement a starting value of 0 by 1 everytime a hit is registered. This method prevents a hit count from ever needing to be compiled by counting records in the hit detail table. *Table - ' redirects'* *ID* -- int #unique ID *domain* -- varchar(255) #users can select several domains as root domain *subFolder* -- varchar(30) #subfolder *redirectUrl* -- varchar(5000) #destination URL *createdDate* -- datetime #date the redirect was created *duration* -- int(11) #how long the redirect will last. default is 3 years. *status *-- bit(1) #active, suspended, etc *lock* -- bit(1) #redirect url can't be edited/changed. multiple owners force lock *unique* -- bit(1) #redirect can't be re-used by others / prevents multiple owners *custom* -- bit(1) #subfolder is custom entry *extRedirect* -- bit(1) #redirect url is external site *type* -- smallint(6) #what kind of redirect it is for. simple redirect, ad, page, etc. *Table - 'redirectOwners'* *ownerID* -- int #unique record ID *ID* -- #ID of redirect *userID* -- #id of owner *createdDate* -- datetime #date the redirect was created *createdIP* -- varchar(15) #ip *Table - 'hits'* *hitID* -- int #hit record ID *ID* -- int #id of redirect *hitDate* -- datetime #timestamp *refererHost* -- varchar(255) #referrer host name *refererString *-- varchar(255) #referrer string #hostname+string = referrer *refererType* -- varchar(255) #browser type *location* -- varchar(50) #country of origin *dnsIP* -- varchar(15) #ip *Table - 'hitCounter'* *hitCounterID* -- int #hit counter record ID *ID* -- int #id of redirect *hitCount* -- int #number of registered hits to a redirect -- -- online documentation: http://openbd.org/manual/ http://groups.google.com/group/openbd?hl=en --- You received this message because you are subscribed to the Google Groups "Open BlueDragon" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
