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 :
- only use atomic attributes but your ER model should take care of that
- avoid the use of unnecessary attribute put them somewhere else (again, ER model should help)
- 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
- 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
- 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
- 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
