Proposed Database Schema and comments

[Micah Yoder <LYoder@cyberis.net>]

Proposed Database Schema

Table: Authors
------------------
int                 Author ID
string              Name
string              E-Mail Address
string              Web Page
...other...status information? Active? Last time logged in?

Table: Articles
-------------------
long int            ArticleNum      -- Article ID Number
string              title           -- Title of article
int                 author          -- Author ID
int                 quality         -- Quality Rating (0-100?)
int                 TimesRead       -- Number times read
int                 TimesRate       -- 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           ArticleNum      -- Article ID Number
int                Category

This will probably require a join and could be tricky.  Does MySQL
support joins?
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.

That's all I have for now.  I definitely want a real database guru or
two to look at this
before we implement much.