Re: Proposed Database Schema and comments

["Aaron D. Turner" <aturner@best.com>]

-----BEGIN PGP SIGNED MESSAGE-----


I wish I could say if this looks good or bad or whatever, but I don't even
have a foggy clue.  I don't even know what a "join" is, which probably
speaks volumes about my ignorance regarding DB's.  :-)

My dad is a DB guru (25+ years of experiance), but he doesn't know the
differences between MySQL or PG.  I can probably get him to help on the
design side and maybe the feature requirements with you if you want, but
my suggestion is for you to sign up on a MySQL and PG mail list and see
what people say.

One thing, I do know is that you shouldn't keep the "rating" of each entry
stored if you're keeping the total rating and the number of ratings.  The
"rating" is derived data, and therefore shouldn't be included.  (My
dad has a poster of the "Laws of DB's" or whatever it's called hanging on
the wall of his home-office.)  We should be able to do on the fly division
using PHP or a CGI, and it reduces the quanity of writes. 

Two other things:

1) Should the file systems be RAID5 for redunancy or will the performance
hit be too large?  (Ie, we should mirror)

2) Can we setup the DB to be high-performance read, and have any writes be
placed in a "holding" DB which would then be sync'd to the master each
night?

Or am I just worrying about nothing? :-)

later...

- -- 
Aaron Turner           | Either which way, one half dozen or another. 
aturner@pobox.com      | Check out the Red Hat Linux User's FAQ Online!
www.pobox.com/~aturner | http://www.pobox.com/~aturner/RedHat-FAQ/
All emails from this account are PGP signed.  Lack of a signature is "bad".
PGP Key fingerprint = FB E1 CE ED 57 E4 AB 80  59 6E 60 BF 45 1B 20 E8


On Wed, 28 Oct 1998, Micah Yoder wrote:

> 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.
> 


-----BEGIN PGP SIGNATURE-----
Version: 2.6.2

iQCVAwUBNjfRzTM3jpXy1kJtAQGwpAP/dcGi3VZdQXZkKJupEFJO1H9hOSaGuqo5
9c2ooHLCjxLIwXijTMP6tiwp7KPVbIQbHgSU/5oOkVPJG1YZYc3QNA6ve/7xN0YA
dqIhiNYzEslWxDwtynCcM0byAWg85W5rDKFs8pfLtyVNpSBO3RwYwLIDBPZ4rj07
HhH9bOXevlk=
=71Ut
-----END PGP SIGNATURE-----