The database skeleton
I’m starting to design the database. Not having studied a lot of database theory, I find it slightly intimidating, especially with possible scalability concerns in the future. But I’m not going to let that paralyze me. If it ends up having problems, then I’ll fix them and move on. Perfection is rarely achieved on the first try. It’s better to get something up and then tweak and iterate until you get it right.
It is tempting to try to read through some database theory books before I get too far, but I’m going to resist, if only because I know that’ll stop me dead in my tracks. Better to do it in parallel. (I’m saying this more to persuade myself than for any other reason. :))
As a brief overview of what’s going to go into the database, here are the objects/entities I’ve come up with so far which need to be represented, and I’m guessing each will be its own table:
- Individuals
- Families
- Sources
- Research pages
- Media
- Users
- Files
- Translations
And some thoughts on each:
Individuals
Has a collection of metadata associated with each record. Need to be linked somehow.
Families
Basically the same as individuals (collection of metadata). I’m not sure if these will be linked or if we’ll rely on the individual linkings.
Sources
Still not sure if I want to go with an elaborate citation system or if it should be more free-form.
Research pages
Each page can have lists, notes, pictures, other media, etc. Not sure how to store it.
Media
Pictures, etc. Right now there’s overlap between this and the research pages. I’m not sure yet how I’ll resolve that. Research page pictures will probably end up being more pictures of documents and such, whereas media will be pictures of individuals and families. Maybe. Hmm…
Users
List of users on the system. Usernames and passwords, first and last name, last login, that kind of stuff.
Files
A file can be a local Beyond store (on that server) or a link to another Beyond server (or a PhpGedView server or Family Tree, for that matter). (If you want to open a PAF or GEDCOM file on your hard drive, will it convert it to a local Beyond store first? Hmm…) You can have more than one of each, of course.
Translations
Support for internationalization.
Technorati Tags:
Posted in Design
Comment feed for this post
For database design books you can’t go wrong with anything written by C.J. Date. He recently published a book through O’Reilly called Database In Depth, (about 200 pgs, and quite approachable).
His buddy Fabian Pascal also has some good books too. Be warned that they aren’t keen on much of what goes on in the database world today, They’re relational purists, but their stuff is all solid from a logical and mathematical perspective, and definitely worth reading. Their website http://dbdebunk.com is also full of good stuff.
Having travelled it for quite a ways myself, be warned that this road (database design) can be long and arduous, and if you really get into the relational model be prepared to be disappointed in finding out that practically none of today’s database engines faithfully implement the model.
– Dan
Mmm, very interesting. Thanks for the heads up!