I'm doing the install module, since that is the part that is the "black box" for most people at this time. Hopefully, this install module will help people understand how to create the plug in. It combines about 3 or 4 "generations" of improvements from Alex <G>
NOTE: I use "database" and "table" interchangeably when referring to links. I'll use "separate" or "external" if I really mean a separate "database" in MySQL. -- just to prevent confusion.
The Links database will have a field "hasReviews" added that can either be boolean "Yes/No" or an integer "0/dd". I'm not sure which the best way to go from a performance reason. "Yes" or a non-zero integer means there is are reviews, and you could use that in the "link.html" or "detailed.html" (or anything that pulled a link record) to link to the reviews page using <%if%> tags. "Yes/No" is more consistent, but a number would give added information. Using a number also means _MORE_ database overhead in maintaining it.
This should be the only change to the Links table necessary -- and it's necessary only from a performance reason, _NOT_ from a data-normal reason.
No changes to the "Users" table should be needed.
We then need the reviews table itself. The mandatory fields are:
ID - this provides a unique ID, so a person could be allowed to add multiple reviews or comments, such as in an appraisal system, if there would be a change over time.
LinkID -- the link to the Links table.
Username -- the link to the Users table
DateTime -- a transaction time stamp
Comment -- a "Varchar 255" field, for a short comment. (may or may not be used)
Text -- a text field for longer "reviews" (may or may not be used)
Rating -- an Int field for a numerical rating. (may or may not be used)
Nothing else is "essential" for the review. I'd like to know what you all feel would be essential?? Maybe something _IS_ better added to the default layout. Remember, you can add _ANYTHING_ you want to the Reviews table, as long as the required fields are left intact. The reason for the Comment & Text fields, is that some sites will want to use a short comment, others will want a full-page review. Some will want both -- a summary/title and a review. This gives the maximum flexibility with the least amount of user input needed.
If each "review" is looked at as a "transaction" then the only information that is needed to be stored in it, is that information necessary for that transaction. Everything else (stats, etc) should be calculated.
The User_Stats table would need to have the following information:
Username (primary key,only one user with that name allowed in the system)
Num_Reviews -- total number of records in the reviews database
Num_Unique -- total number of UNIQUE (ID/Username) reviews in the database
Average_Rating -- the average of that users ratings --
Highest -- has the user ever given a 10?
Lowest -- has the use ever given a 0?
== at this point, the database starts to vary, based on the setup options ==
== lets say the Webmaster wanted a 1-5 rating system (odd numbers better than even).
== the fields added to the database would be ==
D_1 -- number of '1' votes this user gave
D_2 -- number of '2' votes this user gave
D_3 -- number of '3' votes this user gave
D_4 -- number of '4' votes this user gave
D_5 -- number of '5' votes this user gave
This allows the users "distribution" stats to be shown, as well a a "TRUE" average calcuated very quickly on rebuilds.
If a user changes their "vote" and "updates" are allowed, the system would take one away from the old values, and add it to the new values. This could _ADMITTEDLY_ get out of sync, _BUT_ when a "repair" or "refresh" of all the stats was done, any minor corrections would made since the original values were stored in the "transactions" table (Reviews table). Make sense?
The Link_Stats table would be similar --
LinkID (primary key,only one record with that # allowed in the Links table!)
Num_Reviews -- total number of records in the reviews database
Num_Unique -- total number of UNIQUE (ID/Username) reviews in the database
Average_Rating -- the average of the ratings --
Highest -- has the link ever given a 10?
Lowest -- has the link ever given a 0?
D_1 -- number of '1' votes this Link got
D_2 -- number of '2' votes this Link got
D_3 -- number of '3' votes this Link got
D_4 -- number of '4' votes this Link got
D_5 -- number of '5' votes this Link got
Now, I'm also considering an over all stats table, with the data that you might want to put on some summary pages. This would be updated on a "rebuild". It would really only contain 1 line, _OR_ it could contain 1 line for each date the rebuild was run. that way a sysop could track growth. this sort of summary table could be used to plot a graph... for a stats package, later...??
The reason is "Performance" issues. All these stats tables could be calculated from the transactions database in real time, but it would require loads of CPU that could be better used elsewhere. Obviously, there would be an option to turn off "stats tables" so that all data would be dynamically generated. This is only a "minor" set of "ifs" throughout the code. (Remember, maximum flexibility for the most people).
The reason for this longish note, is I want to know what people feel should be added to any of the database structures above!! This is the first step. so might as well work on it now. It can always change... and I'm sure it will, but what do people see as being necessary to add to the tables at this early date??
PUGDOGŪ Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Forum:http://LinkSQL.com/forum
NOTE: I use "database" and "table" interchangeably when referring to links. I'll use "separate" or "external" if I really mean a separate "database" in MySQL. -- just to prevent confusion.
The Links database will have a field "hasReviews" added that can either be boolean "Yes/No" or an integer "0/dd". I'm not sure which the best way to go from a performance reason. "Yes" or a non-zero integer means there is are reviews, and you could use that in the "link.html" or "detailed.html" (or anything that pulled a link record) to link to the reviews page using <%if%> tags. "Yes/No" is more consistent, but a number would give added information. Using a number also means _MORE_ database overhead in maintaining it.
This should be the only change to the Links table necessary -- and it's necessary only from a performance reason, _NOT_ from a data-normal reason.
No changes to the "Users" table should be needed.
We then need the reviews table itself. The mandatory fields are:
ID - this provides a unique ID, so a person could be allowed to add multiple reviews or comments, such as in an appraisal system, if there would be a change over time.
LinkID -- the link to the Links table.
Username -- the link to the Users table
DateTime -- a transaction time stamp
Comment -- a "Varchar 255" field, for a short comment. (may or may not be used)
Text -- a text field for longer "reviews" (may or may not be used)
Rating -- an Int field for a numerical rating. (may or may not be used)
Nothing else is "essential" for the review. I'd like to know what you all feel would be essential?? Maybe something _IS_ better added to the default layout. Remember, you can add _ANYTHING_ you want to the Reviews table, as long as the required fields are left intact. The reason for the Comment & Text fields, is that some sites will want to use a short comment, others will want a full-page review. Some will want both -- a summary/title and a review. This gives the maximum flexibility with the least amount of user input needed.
If each "review" is looked at as a "transaction" then the only information that is needed to be stored in it, is that information necessary for that transaction. Everything else (stats, etc) should be calculated.
The User_Stats table would need to have the following information:
Username (primary key,only one user with that name allowed in the system)
Num_Reviews -- total number of records in the reviews database
Num_Unique -- total number of UNIQUE (ID/Username) reviews in the database
Average_Rating -- the average of that users ratings --
Highest -- has the user ever given a 10?
Lowest -- has the use ever given a 0?
== at this point, the database starts to vary, based on the setup options ==
== lets say the Webmaster wanted a 1-5 rating system (odd numbers better than even).
== the fields added to the database would be ==
D_1 -- number of '1' votes this user gave
D_2 -- number of '2' votes this user gave
D_3 -- number of '3' votes this user gave
D_4 -- number of '4' votes this user gave
D_5 -- number of '5' votes this user gave
This allows the users "distribution" stats to be shown, as well a a "TRUE" average calcuated very quickly on rebuilds.
If a user changes their "vote" and "updates" are allowed, the system would take one away from the old values, and add it to the new values. This could _ADMITTEDLY_ get out of sync, _BUT_ when a "repair" or "refresh" of all the stats was done, any minor corrections would made since the original values were stored in the "transactions" table (Reviews table). Make sense?
The Link_Stats table would be similar --
LinkID (primary key,only one record with that # allowed in the Links table!)
Num_Reviews -- total number of records in the reviews database
Num_Unique -- total number of UNIQUE (ID/Username) reviews in the database
Average_Rating -- the average of the ratings --
Highest -- has the link ever given a 10?
Lowest -- has the link ever given a 0?
D_1 -- number of '1' votes this Link got
D_2 -- number of '2' votes this Link got
D_3 -- number of '3' votes this Link got
D_4 -- number of '4' votes this Link got
D_5 -- number of '5' votes this Link got
Now, I'm also considering an over all stats table, with the data that you might want to put on some summary pages. This would be updated on a "rebuild". It would really only contain 1 line, _OR_ it could contain 1 line for each date the rebuild was run. that way a sysop could track growth. this sort of summary table could be used to plot a graph... for a stats package, later...??
The reason is "Performance" issues. All these stats tables could be calculated from the transactions database in real time, but it would require loads of CPU that could be better used elsewhere. Obviously, there would be an option to turn off "stats tables" so that all data would be dynamically generated. This is only a "minor" set of "ifs" throughout the code. (Remember, maximum flexibility for the most people).
The reason for this longish note, is I want to know what people feel should be added to any of the database structures above!! This is the first step. so might as well work on it now. It can always change... and I'm sure it will, but what do people see as being necessary to add to the tables at this early date??
PUGDOGŪ Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Forum:http://LinkSQL.com/forum