Database Schema

[Micah Yoder <LYoder@cyberis.net>]

For those who haven't seen it.......

And it's slightly modified for those who have....
Proposed Database Schema - LinuxKB

Table: Authors
------------------
int                 id               -- Author ID
string              name             -- Name of article
string              email            -- E-Mail Address of aithor
string              web              -- Web Page
...other...status information? Active? Last time logged in?

Table: Articles
-------------------
long int            id      -- Article ID Number
string              title           -- Title of article
int                 author          -- Author ID
int                 TimesRead       -- Number times read
int                 TimesRated      -- Number times rated
long in             TotalRating     -- Total Rating  (used to calculate average rating)

TimesRead, TimesRate, and TotalRating could be tabulated and updated once per day from logs,
so we don't need to run an update SQL command every time a user does something.

Two other things are category numbers and distributions the article applies to.
We could store that information in the Articles table if we used a DB that supports
arrays (PostgreSQL) AND allows searching of arrays (I don't think PG does that,
but I'm not sure).  Probably a better method is to store that in different tables.
The disadvantage to that is that it will take two or three SQL lookups instead of
just one.  If PG allows us to do it with one lookup and MySQL two, that might tip
the scales in favor of PG.  But again, I'm not 100% sure it can do everything I
want.

So, here are the proposed separate tables:

Table: Categories
------------------------
long int           id            -- Article ID Number
int                category      -- Category # from list

This will probably require a join and could be tricky.
Example, given the category number, get information on all articles in category:

select Articles.*  -- yeah, we SHOULD list all the fields, not *, but I'm lazy here
from Categories, Articles
where Categories.Category = CategoryWe'reInterestedIn
  and Articles.ArticleNum = Categories.ArticleNum;

(I'm a little rusty in advanced SQL.  Let me know if this query is wrong!)

Fortunately, we can and should just generate the article list once per day or perhaps whenever
an article is added.  We don't want to run this every time someone accesses the index
page!

Table: AppliesTo
-------------------------
long int        ArticleNum
int             dist          -- Distribution ID (1=RH, 2=SuSE, whatever...)
   -- Also other things... 0 = linux kernel, X = GTK version, Y = Postgres version, etc
real            minver        -- Minimum and maximum version numbers.  -1 / +100 = open ended?
real            maxver

An article could have several entries here.  Like, it could apply to all RedHat versions
4.2 and before, and all Caldera versions 1.2 and before.  This is only used if the user
sets criteria in some form, and will search for a list of article numbers that specify
the criteria.  Then a join on this and the Articles table will probably be required.
Ouch.  Hopefully it won't be TOO slow.

I guess this is a rather optional feature and doesn't necessarily have to be implemented
right away.  But it would be sweet IMHO.  For a lower-tech solution, we could just do what
M$ does and say "Applies to: ..xxxx..." in the article itself.

Another use for this table could be to generate "Applies to" information given an article
number.  This would not require a join, and actually should be quite fast.