Table for Locations over time

[Western_Neil]

Hi all


Next question (does he ever stop)?

My data ranges over 200 years, and in that time locations have change Name, States and Countries. Most DB seems to use City, State, Country, but then again boundaries don’t move very often.

Again I see ways of storing this:
1) Use 1 Table and have To From dates and a link to the next name.
2) Use 2 tables (a Geo Table (Locations don’t move (much)) and a Political Table (Man named things do)).

The first one can return false data if the code doesn’t find the real bottom. The second one feels wrong somehow .
Comments??
Neil

[nrgins]

Hello, Western Neil. Southwestern Neil here. :-)

You didn't really specify how the data is used or what the data is. But, in any case, based on what you wrote, what I would do is have a separate location table, with a start and end date for each location (end date for the current location would be blank), and then specify the location there, linking it to your main entity on the entity's ID field, which you would store as a foreign key in your location table.

Then, if you want to get the current location for an entity, you get the location with the most recent start date and use that. If you need to go back historically to a past date, then you'd find the location for which the date is between that location's start date and end date.

I don't know what you mean by "a link to the next name," but yeah, that sounds wrong, whatever it is.... ;-)

[orange]

I agree with nrgins
- if location names change you need some mechanism to record same
- you haven't told us much of your overall requirement. We seem to be adding pieces...

Can you step back, and tell us in plain English --what is the subject involved? What is your goal?

[Western_Neil]

Sorry about that guys. I got focused on what I wanted, and forgot to bring you guys along. I do this alot , so anyways

I’m new to MS Access (I have it, but I’m not set on it), but have worked with Relational Data Managed Structures and written COBOL programs using it.

I’m trying to create a database to track Lineages and Order of Battle (OoB) of military units in the 20^th century with an emphasis on Canada and WW2. Much of the data is date based but may not have a compete date (i.e. early Dec 1941) or even a fixed start. The DB will track the Name, Size, Branch, Superior, Level, Location, etc., and the date. Each of these attributes can change at different dates but may not exist at any point in time. With examples I can explain how this all works but it’s not worth while until someone says “sure I will Help”!

My flat file is about 12,000 records, but it is NOT totally normalized. I don’t want to change the flat file structure until I have a DB structure that will fit and handle the data quirks as I know it. The commercial aspect of the project is very low and it is being self-funded. All credit and acknowledgment will be shared in proportion to the help received. I am willing to discuss this projects’ data structure in this forum or in private as the coach wishes.
So the Question is …

Can someone coach me in designing the DB structure and get me started on this

[orange]

Neil,

Here are 2 tutorials form RogersAccessLibrary.
1) Class information

2) Widgets

You have to work through (30-60 minutes) them, but you'll learn how to design a database.

You should also watch videos 1,2 and 4 from this series


Get a clear description of what you are trying to do. It's often more difficult than you think, but it will help you and it will help in communicating with others.

Good luck.

[nrgins]

I don't know about orange, but I just meant like a little more information about how the locations were going to be used, not the whole ball of wax. :-)

In any case, did my response about how to set up the locations table solve this for you?

[Western_Neil]

Sorry about that, that pre-written response got away from me but it does say what this about.

Getting back to this threads' question (their I go again),
Some examples of places changing names:
1) Fort Macleod has changed its name to Macleod and back again (offical I think once).
2) Kitchener, Ontario was called Berlin before WW1.
3) Alot of Quebec places restored their french names in the 60-70's.

Boundaries have also moved as Alberta was part of the NWT and was in a couple of Districts as Canada grew. The Military Districts boundaries have also changed over time, and have been dropped from usage. This means the DB should not return info out time.

All these are small bits now, but if I want to historical correct I need to account for them. No good talking about the Newfoundlanders from Canada in 1916 or even 1944.

Neil

[Western_Neil]

I know what I want Orange, but some tabby has played with my ball of string.

I have found it hard scoping this thing out, I keeping finding rabbit holes to go down and all getting lost in the details.

Will try harder Neil

[nrgins]

So that's a "yes," the Locations table situation is resolved?

[orange]

Look at the tutorials and get a sense of the level of info required. Knowing what you are trying to do is more than half the "battle" --sorry for the pun.

[Western_Neil]

Sorry SW Neil but its a hold. I'm unsure of how this will work, and Orange has given me some homework to do . I was hoping for a canned answer but it looks like I may have to do some custom work.

I shall return, Neil

[nrgins]

OK, cool. FWIW I find that when developing databases, it often helps to just model data in different ways with different table designs. Sometimes you have to play around with it a little until you get it right. That, of course, implies that you understand the basics of relational table design. So it's a combination of knowledge and just getting your hands dirty and playing with it.

One day we'll be able to say, "Cortana: design a database for me where...." But we're not there yet. ;-)

[orange]

Further to nrgins comment, here is a post I made in response to a similar situation on a different forum. See the stump the model link for more info.

Good luck.

[Western_Neil]

I get invalid link, but the forum does show up
Neil

[orange]

Below is a copy/paste of the link I pointed you to.

I added some links in my previous post re Relations/relationships.

If you are new to database and Access, then working through on of the tutorials at RogersAccessLibrary may be extremely helpful to you. They start with a problem description; lead you through a procedure to identify entities and attributes (tables and fields) and relationships; to produce a data model (blueprint) for your database that supports your requirements. Each tutorial is similar and takes about 45 minutes to 1 hour to complete. You have to do work through the tutorial, but what you learn can be used with any database and/or dbms.

Here are a few tutorials:

Class information database
Catering Business

You may also want to review Normalization. This is the first of a series of related articles.

Also, you will find that using a naming convention that does NOT allow embedded spaces in field and object names will save you many syntax errors as you progress.
Better to use alphanumeric and the underscore(_) character only. And do not use a number as the initial character in a name.

Here is a link to "stump the model" post that will help you test your model before getting too deeply into trial and error mode.

Good luck with the project.
__________________
DebuggingTips
ErrorHandlingTips