Database Building help.

Discussion non-related
User avatar
[FnG] pyxie.T32
Posts: 5403
Joined: Sun Dec 14, 2003 2:37 pm
Location: London UK
Contact:

Database Building help.

Postby [FnG] pyxie.T32 » Wed Jun 09, 2010 10:53 am

Here is one for all of you office bound people,

First yay me I just got a job, I'm no longer dole scum!



now the job:

I work for the council as a 'memorials recordings officer'. That means Parish records, the job entails fairly simply photographing every headstone in the Borough, all 30,000 of them - no lie. I then build a database for historical purpose. It should be searchable and at some point may be online in some form or another. I will need details like

Name
Age
Location
stone type (marble/granite etc)
Gender

And probably a couple more, ideally all cross referanceable, idea being you want to find your great grandma, you know what cemetery she is in and the year she died you should be able to put those values in (Mabel, 1961, Twickenham) for example and it gives you a list that matches those details.



Now I know you can with photoshop 'tag' images I think with multiple tags, or I could create the mother of all excell speadsheets with every name down the left and all options along the top, the names can link to images? But these both feel like slightly ineficciant ways of doing it, is there a program method you lot would recomend?
Image
Gravity, not just a good idea, ...It's the LAW!

User avatar
[FnG] LcNessie
Site Admin
Posts: 5360
Joined: Sat May 07, 2005 11:44 am
Location: The Netherlands, Delfzijl
Contact:

Re: Database Building help.

Postby [FnG] LcNessie » Wed Jun 09, 2010 11:25 am

I really think that a relational database is the way to go here. I don't see the need for very special functions within the database, sounds mostly "IOQO" (Input Once Query Often) to me. However, the database scheme seems just a bit too complicated for a flat file akin to Excel.

A MySQL or PostgreSQL solution sounds like the way to go for me. With a bit of self-training, you can design yourself a nice little data-schema to accompany this. You could even go for *choke* an *cough* access database application *puke* *scrubs mouth with liquid green soap before brushing teeth with pure H-Cl*

I would, however, use two or even three or four fields for "name".

Calling name (For example John)
Given name (Might be a copy of calling name, but could also be more lengthy, for example Matthew Bartholomew Johannes Maria Petrus)
Family name ( Carpenter, Johnson, Clooney, McFadden)
Family name prefix ( the, of, from, du, van der ) (A common family name in the Netherlands is "van den Berg", but "van den Bosch" is also quite common. it's classified under "Berg, van den" or "Bosch, van den" )

As a unique key, use an incrementing long integer. The accompanying picture should get the same number, but add .png at the end, or .jpg, or whatever format you are thinking of. PNG is completely royalty free and lossless.
With kind regards, Mathijs
Image
Image
WII: 8832 9602 6333 9837

User avatar
[FnG] Lambik
Site Admin
Posts: 2519
Joined: Tue Nov 11, 2003 5:45 pm
Location: The Netherlands
Contact:

Re: Database Building help.

Postby [FnG] Lambik » Wed Jun 09, 2010 2:45 pm

I think your biggest problem won't be the database setup, as not very much can go wrong there,
but I think linking the images to the correct information will be hard.
A thing that comes to mind is EXIF data, these days EXIF data stored within the images/photo can also contain GPS info(depending on the camera), maybe that can be usable information to you.

Depending on what size/resolution you need the images to be, you can choose to store the images as BLOB in your database, which makes the linking proces more reliable,
but it's also a bit slower then images stored in some folder. So it depends on how many 'views' you expect.

I also think you can use more fields (like Nessie mentioned), with dates (birth/deceased) and optional fields like maidenname.

So in short, collect as much information about what you want to store and search on, define the quality of your images, research how many queries per time are required,
then if you have a more or less complete picture of what you want to store and search on, determine how this information is related to each other.
I mean one on one information, and one to many information.
Like persons, name, familyname, birthdate, deceased date are directly related (one on one)
the graves can contain one or more persons (familygraves), so that is one to many, this requires a separate table, same goes for the cemetery.
(note you can store the cemetery info allong with the images(graves) in one table, but storage wise that is not recommendable.

In your case as example :
table person:
person_id
img_id
cem_id
name
family_name
family_name_prefix
date_birth
date_deceased
remarks

table additional_names: (as some ppl can have loads of firstnames, it will be hard to create columns for that, also some marry over and over, and can be known by other family names)
add_id (not really required, but it makes it easier to update this table)
person_id
name
type_of_name (firstname/marriage name/calling name)
remark ( I often add this type for comment use, but also as often never used it so you may skip it :P )

table images:
img_id
img_name
img_data ( BLOB, or else image storage location)
img_exif_info (you may require more fields for this)
img_remark
cem_location_number (I believe every cemetary works with numbers as 'addresses' to the graves.)

table cemetery:
cem_id
cem_name
cem_location
cem....

The bold fields can be defined as foreign keys to make sure the data integrity is maintained.
The use of 'index' can speed up a query quite a bit.
But like I said at the start, shooting the images is one, but linking them needs a lot of attention, maybe someone on the forums can hand you some ideas...
_______________________________________________
"I am not a complete idiot . . . some parts are missing " :crazy:

ImageImage
Image

User avatar
[FnG] pyxie.T32
Posts: 5403
Joined: Sun Dec 14, 2003 2:37 pm
Location: London UK
Contact:

Re: Database Building help.

Postby [FnG] pyxie.T32 » Wed Jun 09, 2010 3:32 pm

Thanks Nessie, Lambik,
some things to think about there,

I quickly knocked up a excel sheet which works as a table and is searchable, which works fine for internal use but to then get that on a website I will need to read up on the exciting world of SQL data servers I think :( Some sort of relational database is whats required I fear, something I'm going to have to read up on very fast indeed.
Image

Gravity, not just a good idea, ...It's the LAW!

User avatar
[FnG] Sphinx
Posts: 2067
Joined: Wed Oct 18, 2006 6:07 pm
Location: Normandy, France
Contact:

Re: Database Building help.

Postby [FnG] Sphinx » Wed Jun 09, 2010 3:42 pm

On a different note, dont forget to request decent hardware, software licenses, security etc. for hosting the system. Can get quite expensive especially if you are using SQL server.
Image
You can take my chocolate when you pry it from my cold frozen fingers
Image

User avatar
[FnG] Hanno
Posts: 2714
Joined: Sat Oct 30, 2004 8:16 pm
Location: Devon , SW UK
Contact:

Re: Database Building help.

Postby [FnG] Hanno » Wed Jun 09, 2010 4:12 pm

Hey forget all that database stuff (it brings back nightmares to me) :slaphead:

But congrats on the job! :yahoo:
Image
Hanno's Law:
Anyone who says they know what's going on are probably lying.
Anyone who believes they know what's going on is dangerous and should be avoided at all cost.


Return to “Everything Else”

Who is online

Users browsing this forum: No registered users and 3 guests