Relational databases - 2

[Richard T Eger]

From 12 O'Clock High!:

Zamax
Databases info
Mon May 3, 2004 14:33
212.210.71.3

Since my personal database for WWII pilots is in .doc format, I would know if your databases are in the same formats and which is the best.

TIA
Zamax

[Richard T Eger]

From TOCH!:

Jim P.
I use MS Access - though only as flat file tables (nm)
Mon May 3, 2004 15:25
64.223.199.2

[Richard T Eger]

From TOCH!:

Jim P.
Access can sort on any field with the click of a mouse, or..
Tue May 4, 2004 22:03
64.223.199.2

a query can be written in minutes.

[Richard T Eger]

From TOCH!:

Pavel
I use MS Excel - good for searching and sorting (nm)
Mon May 3, 2004 16:01
147.32.201.33

[Richard T Eger]

From TOCH!:

John Beaman
Re: Databases info
Mon May 3, 2004 16:46
24.163.23.138

I also use MS Access as "flat" and relational files. It is extremely simple to build searches and queries.

[Richard T Eger]

From TOCH!:

Don Caldwell
I use MS Works 3.0 (on a Mac)...
Mon May 3, 2004 19:49
216.99.65.10

...it's simple to set up, troublefree, & fast to search. Of course, it's "flat".

[Richard T Eger]

From TOCH!:

Mark Huxtable
Depends how bad your addiction is
Tue May 4, 2004 12:01
149.228.54.2

If it is all about pilot personal details, a flat-file Excel spreadsheet will do you fine. If you want their personal details AND their units, including bases, dates, orders of battle etc. AND their claim details, you'll need a relational database like Access.

Bottom line: limit your addictions.

Cheers,

Mark

[Richard T Eger]

From TOCH!:

Frank
fg52home@beeb.net
Relational databases
Tue May 4, 2004 21:16
62.252.192.4

Hello Mark

This might warrant a reply off board?

Any good introductions to relational databases you can recommend?

I find Access totally frustrating as I don't really UNDERSTAND databases, so can only use it flat! (So I don't 'cause Excel is easier!)

Any help very welcome

Cheers

Frank

[Richard T Eger]

From TOCH!:

Andreas Brekken
andrebre@online.no
Relational databases - an intro
Wed May 5, 2004 10:18
80.213.189.96

Hi, Frank.

The best and cheapest way of obtaining the basic understanding of how a relational database works is to:

1. Find a pencil, an eraser and a pile of blank paper sheets

2. Search for the words 'relational database introduction' in Google

3. Read through the theoretical foundations and examples on some of the articles You like best

4. Try to order the information in Your head and on the paper as good as You can.

5. Test it out in for example Access

I found that the hardest nuts to crack was to totally understand how to use a many-to-many relationship, and also to obtain perfect normalization of the tables.

MVH,
Andreas

[Richard T Eger]

From TOCH!:

Frank
Thanks Andreas, sharpening my pencil and brain! (nm)
Wed May 5, 2004 19:24
62.252.192.4

[Richard T Eger]

From TOCH!:

Vinnie O
Draw data models
Thu May 6, 2004 04:39
68.50.200.226

The theory is largely impenetrable if you start cold.

Start by drawing "data models". After you've played with the diagrams of the entities/tables for a while on paper, you will begin to understand what Date & Codd are really talking about. Then reading their stuff is straightforward, if not easy. They waste a lot of time on the underlying logic of the mathematics of set theory, which is about as useful to building a data base as a complete understanding of thermodynamics is to driving a car. One DISCOVERS the organization of the data by thinking about it.

You can download FIPS-184, IDEF1X for free from a number of sources. It tells you all you need to know about the general practice of data modeling and Entity-Relationship Diagrams (ERD) and such.

If you have any questions, let me know. I do data modeling as a full time job. I like it almost as much as military history.

The cheapest way to get a REAL SQL data base going is to check PriceGrabber or one of the other software searches and buy a copy of PowerBuilder. I got 8.0 for $25. Any version will do. PowerBuilder comes complete with Adaptive Server Anywhere, a fully compliant SQL data base.

Access is some bad joke from Microsoft. In most ways dBASE III was better.

Oh, you'll notice once you start constructing your data model that you QUICKLY get 15-20 entities/tables. This isn't a problem. My WW2 data base probably has more than 100 (haven't bothered to count them in a long time).

The basic idea of a relational data base is "one fact in one place". This automatically improves the accuracy and ease with which the data can be maintained. A point I picked up from the intelligence community though is that for each fact you really want to record the source, the precise of the data in the source ("late 1942" gets recorded as "1 December 1942" because the data base column is of type DATE, so the precision of the cited source is "approximate" or "?" as opposed to "wild guess" or "???"), and your confidence in the source (many of the classic books from the 1970s are now known to be VERY sloppy). This means that you quickly quadruple the number of columns in your tables because you have a pattern of pilot_name, pilot_name_source, pilot_name_precision, pilot_name_confidence. Etc., etc. I can talk for DAYS about this.

If you're interested, I can paste some ERwin models into Word or something so you can see what I'm talking about. ERwin is simply the best tool for drawing data models, but it's gotten pricey over the years.

Vince O'Mahony

[Richard T Eger]

From TOCH!:

Jim P.
There was an individual I know who wanted to...
Tue May 4, 2004 22:02
64.223.199.2

try and develop a fully relational db for all this LW stuff like Mark mentioned. This is DEFINITELY not an application suitable for this in large scale, unless you're really a glutton for punishment. And it boils down to this - name one piece of data that can uniquely identify a pilot (or plane) and is universally complete to carry thru-out a db. Name, WNr.? Keep it simple.

[Richard T Eger]

From TOCH!:

Frank Goodridge
fg52home@beeb.net
That's why....
Wed May 5, 2004 01:11
62.252.192.4

Hi Jim

That's why I posed my question.

ONE individual person, or airframe, makes the starting point in a relational database (If I understand correctly?).

I believe that the Netherlands Recovery Group are working on a database centered on an 'Initial ID' by which they will link all further recovery information? THEN they will tie it all together under one number? When they know what they have found?

I have piles of; photographs, PRO files, and all the rest I would like to catalogue (database) so when I'm gone I can pass it on in a sensible form. And put it on the Web now.

I want to share, but I need to know how to direct my efforts. And I DON'T understand databases!

This is the scientist in me talking, we like to publish!

Cheers

Frank

[Richard T Eger]

From TOCH!:

Jim P.
Unfortunately a name is generally....
Thu May 6, 2004 17:26
64.223.199.2

not unique enough, there are way too many unidentified airframes - no WNr., SKZ or whatever. You could assign your own keys or unique IDs, but managing it would be a nightmare. The sad part is I know of an almost perfect database for this type of stuff, but its a commercial document archiving system - no personal desktop version.

[Richard T Eger]

From TOCH!:

Anders Berg
MS SQL personal edition
Tue May 4, 2004 21:44
148.122.26.199

but it started out in access. Data is normalized and relational.

[Richard T Eger]

From TOCH!:

Frank
Anders, Please see below (above on LWAG)?
Wed May 5, 2004 01:13
62.252.192.4

And tell us how?

Thanks

Frank

[Richard T Eger]

From TOCH!:

Anders Berg
As an example
Wed May 5, 2004 08:23
148.122.128.182

lets take a person.. You create a table containing first_name, Last_name, Date_of_Birth, Date_of_Death etc...
Now, you cant use his name as a uniqe ident so you have to make a seperat field for this.. Lets call it PersonID. In access you would set that field as 'autonumber' and set it as indexed, no duplicates. Lets say you want to keep track of the persons rank - you create a 'Rank' table, containing a Rank_name, RankID in the same manner as the PersonID in the previous table, and ev. more info you want to describe a rank. To link the person with a given rank, you create a table to keep track of the link. That table - lets call it Person_rank contains PersonID (long integer), RankID, from_Date, to_Date - and you set the primary key to 'PersonID' + 'RankID' + from_Date. Now you have a relational database... And the same you can apply to units, planes, link units with airfields, link planes with units and Personell etc...
Hope this gave you some ideas

[Richard T Eger]

From TOCH!:

Frank
Thanks Anders, useful! (nm)
Wed May 5, 2004 19:23
62.252.192.4

[Richard T Eger]

From TOCH!:

Jim P.
How do you manage the Person ID?
Thu May 6, 2004 18:08
64.223.199.2

If I'm reading what you said correctly, you've essentially created two files, person & rank, and then a third to store the pieces of data tying file 1 & file 2 together. I hope this was just for an illustration of a relational db - because by the time one gets done creating a, for instance, 10K entry person file and trying to 'link' it to a rank file (or plane or awards or whatever), while data may be 'normalized' its no more efficient than having saved the rank in the person file in the first place, and certainly requires more time to build. You might have more flexibility if you're tracking a certain type of data, one guy's promotions for example. And please realize I'm playing devil's advocate here - what Anders has stated is certainly valid and doable, but I think oversimplifies things to a big extent. One huge issue I see is the maintaining of the person ID (whatever unique ID you choose) - the machine can't link one file to another without that ID and someone has to manage that, the software doesn't do it automatically. This is why I always tell people to KEEP IT SIMPLE - that is unless you're a developer or really serious programming hobbyist. I've tried to think of ways to do this effectively for years - and quite frankly, outside of my professional life (systems analyst), I'd rather pursue my hobby than duplicate my professional life at home.

[Richard T Eger]

From TOCH!:

Frank
This could get boring! Too long if you are not interested.
Thu May 6, 2004 23:17
62.252.192.4

But I'm not bored!

I'm only 'interested' in Access because it is what my students use - I have no delusions about Microsoft.

I'm also constructing my Family Tree which involves creating gedcom files - guess that really is relational? And I have been very careful there not to tie together 'relatives' until I really know they are (probaly are!). I've found memories are fragile!

It seems to me that Jim is addressing a similar point?

The software is not important (but if it works well it helps) but the validity of each item of information is?

It seems we need to use a fixed key, for information we are sure of. And then an ultimate key, to bring all the different fixed keys together - when we know the common factor? First Rule of Databases - only key it in once!

In the end I come back to one of my original points - none of us are getting any younger and the longer the knowledge is just in our heads the greater the danger it will be lost forever.

If it is possible to make a record, in a transparent way, for future generations then I would like to. And that won't help me decipher my (deceased) father's war time diary. But I do my best.

And Chelsea AND Newcastle are out of Europe! ;-)

Cheers

Frank

[Richard T Eger]

From TOCH!:

Anders Berg
Identifier
Fri May 7, 2004 08:23
130.67.132.195

As I wanted to build a relational DB, the only way was making a key yourself (or system generated). True, it would be problematic/impossible to keep track of the ID using just tables and queries - but I use a VB application for mainenence. Frankly, I can't see how it's possible to create a db containing links between persons, units, planes, pictures, references etc without using a relational design. Seems there are a few around with interest in LW, and who also got knowledge in DB-design / programming.
If anyone is interested I can email a SQL-script that shows how the db is designed.

[Richard T Eger]

From TOCH!:

Andreas Brekken
andrebre@online.no
Relational or not relational - that is the question!
Wed May 5, 2004 10:10
80.213.189.96

Hi, guys...

Interesting how this question turns up from time to time.

I guess You all have come to the point where the Excel spreadsheet approach and the table in a word document approach simply does not work anymore.

Developing a full blown relational database for this kind of stuff take some knowledge and a bit of work.

I have been doing this now for some years - my drive to do it being that I have a LOT of Luftwaffe documentation in various forms, and do not like to use a lot of time to search for information - also I hate to enter the same information into a computer more than once....

The best choice for this kind of information is ANY relational database.

The choice depends on what You are going to use the information for later on.

I have built a MS SQL database, the main reason being that it is what I now use professionally, and the fact that a MS SQL server are very friendly towards ASP driven webpages.... also - as seen below, I am aiming towards using the database online, where an MS Access database which is a good alternative for low user number applications simply will not work well with a heavy load.

As some of You know, and some have seen the first build of it, I am launching my (what was to be a Luftwaffe losses database but has grown to be a pilot database, war diary, claims database and photo archive....) online system in June of 2004.

I am currently quite busy with this, and my other business (I have to earn some money and try to take care of my loved ones now and then....), but if anyone would like further info on the topic and how I went about it - pls contact me by mail.

Regards from Norway,
Andreas Brekken