Update, it seems I cant link the application like that so here is the url :
application url
Beware reader, it's in french. If you're interested I wrote it first in OOBasic as a macro for openoffice base, then rewrote it in java and wrote the web frontend in javascript using meteorjs

some time ago I had to build an that would generate shifts calendars for the interns at my town's hospital I had to take into account quite a bit of factors and ended up having to make many different kind of searches to check and cross reference columns values.

I say : plan for growth, have a look at this page to get a good start, if performance is not the main issue (say you wont have a metric ton of traffic and requests over small time) this should allow you to build with a good design from the ground up

from my database course at uni I'd say that you would want to stop thinking in terms of fields and start thinking about what real things you want to document.
As you build your ER model you will quickly identify what fields you need
Then the next step is to get rid of what is legal in the ER model but would slow you down and make growth/maintenance hard.

This step is (in my mind at least) very important because even if you dont have to regularly add new features if you do it well maintenance will be a breeze (at least compared to the alternative state of things).

Worth to have a look at :

  1. only use atomic attributes but your ER model should take care of that
  2. avoid the use of unnecessary attribute put them somewhere else (again, ER model should help)
  3. Keys are good, dont have attributes in a table determined by anything else than a key, you'll thank yourself when writing new queries down the road
  4. keys should be decided with the domain in mind,not the implementation at least that's how I saw it when I learnt about them
  5. Multi valued dependencies was my least favorite, for this one I'll only say sit down with a pen and some paper, if you have done the previous work the best way you could their should not be too much trouble.
    try to find examples onlines this is a tricky one
  6. Boyce-Codd is easier than the previous one but then again I advise the study of multi valued dependencies, functional dependencies and such.

This list is obviously incomplete, there are also the fifth and sixth normal forms but I have not studied them (yet) also take everything I told you with a pinch of salt since I'm only a student and while interested by dbs I'm not fascinated by them.

last note : I tried to write a dbms in awk but gave up this coder did not, its a fun read ^^
Cheers and as usual please correct any mistake you see I'll happily strike them out and update my personal wetware database

In reply to Re: OT - SQL choosing a schema for index tables by QuillMeantTen
in thread OT - SQL choosing a schema for index tables by bangor

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.