More...Relationship help needed...Part 2...

[moonunit]

I've already posted a question (and had the answer) [Relationship help needed...Before I go mad! (Topic#171277)] - since which time I've continued nurdling on the same db.

To summarise, I'm working on a Mileage Database and Itinerary Planner.

Currently this consists of two tables...

Table 1: Routing

• Field: rCity [text // match field] [*Should this be a global?]
  // enter the first four departure city letters
  Portal: Field 1: RouteCalc [Calc text]
  Field 2: Miles [number]
  // Portal row is selectable, highlights and places contents of portal fields into two temp fields for scripted "assign" to Itinerary layout

Table 2: Mileages

• Field: Record ID [auto-entry serial]
  City1 [text]
  City2 [text]
  City_City [calc text; city1 & city 2]
  City_City_Inv [calc text; city2 & city 1]
  SearchCalc [calc text; Left ( city1; 4 ) & ¶ & Left ( city2; 4 )
  RouteCalc [calc text; If ( Routing::rCity = Left ( city2; 4 )
  ; City_City_Inv ; City_City )

Tables are related by equijoin rCity = SearchCalc [no new record creation]

Problem 1 is mechanical...

I need to be able to add new routes to the Mileage records from within the Routing table, but outside the portal.

I've tried spinning out a second Mileages table allowing record creation, making rCity [global] = City1 in the Mileages 2 table and up to a point I can make it happen, but where city 1 already has a record (ie London has 10 possible 'to' routes already) it becomes impossible to do anything other than edit the first 'London' record - which is of course pretty much the last thing you want to do...

I've also tried scripting, but this is messy too. Whilst the portal shows a concatenated from/to, a new record requires three fields [to/from/miles]. SetField won't work - why? So I'm left copying/clearing/layout changing and pasting, three times with the window flickering like a strobe!

The second problem is more cerebral...

When this comes off, this db will be used to calculate costings, mileages and trip times for UK/European road-trips by somebody with no clue how FMP works!

In their work year they might create a 1000 itineraries and I would guess that over time they would amass 50,000 'from-to' routes.

Given a 10 row portal, my little test has already created more than the portal window's worth of 'London' records and as London will always be the start point, every itinerary will begin with London regardless of the destination - at a wild guess, I'd say that of itself, London could end up being 600 records...

So the cerebral question is does a portal make sense?

Does anybody think that my approach is right or completely wrong?

I've attached a slightly ragged (evidence of organic tinkerings) copy of the db in question which you're welcome to pull to shreds in any attempt to understand what I'm banging on about!

Tim

[Sanjai]

Hi Tim,

I have gone through the description but I would be able to look at your files in the evening since I have FMP7 in my home laptop and in my office hours I work on 5.5.

Looking at your description, can we start to work on the design of your database?

As you have written above that the database is designed to calculate the costings, mileages and trip times. Based on the fields which you have mentioned in your two tables and the fields which I think would be needed to store the required data, I think we would need the following tables with the folllowing fields.

City: CityID, Name, State, ZipCode

Routes: RouteID, DepCity, DestCity, Distance(in Miles), cRoute(Calculation: DepCity&DestCity.

Relationship: cRoute with cRoute to check duplicate routes.

I am not sure how you would calculate the costing information so we can discuss it more and add the required new fields. We can make the tables more complex if you would like to introduce a VIA_ROUTE field which would indicate that which route would be taken from DepCity to DestCity.

This would help to keep track of more than one possible ways for the same route.

Explaination: The City table would be our master table where every new city we would enter.

The Routing table would be the trasaction table where we would have a layout with the following fields:

RouteID: AutoGenerated

DeptCity: Popuplist from City table.

DestCity: Popuplist from Dest table.

Distance

Advantages of the above database schema:

1. You can search on routes not only on city but from zipcodes also.

2. So far we cannot see if we would need a portal for the above row.

3. Information is normalized to third level.

4. City information would not be duplicated.

5. Easire to search and avoid duplicate routes.

6. All the advantages of RDBMS.

Let me know your feedback if we are going in the right direction then we can continue our discussion.

Suggestion: In the route table, we can store the zipcodes along with/instead of city names that would help us to avoid duplicate city names. Although for the end user, we would always show the city name but internally we would keep track of zipcodes.

Edited November 10, 2005 by Guest

[moonunit]

Hello Sanjai,

Thanks for your quick response...

Firstly let me explain that I start these databases as a problem-solving challenge, rather than planning the design properly in advance: I accept that this isn't the way to do it!

Starting in the middle, costings are generally speaking outside most of the data we're inputting. Mileage is totalled, 10% is added and a gallon/price equation works out the fuel cost. Vehicle & driver time is based on the total number of days for the period. Ferry costs have to be factored in [i planned to add in a ferry trigger so that when a particular route is set, the required ferry is listed - from a Ferry look-up Table inclusive of prices and operators] Road tolls and taxes are an allowance rather than a fixed number. And then there may be a whole raft of additional costs...And there needs to be an allowance for at least two currencies. Driver hours (trip time) is a combination of average mph calc versus legal driving time allowances (+/- ferry times).

Via Route...I planned to add in a note to a route facility but limiting this to a single Via Route is probably too restrictive - but what concerns me more is not forcing the user into entering more data than they do now.

City: CityID, Name, State, ZipCode... State will become Country; Zip isn't really going to happen - when somebody knows the zip here it's location specific; nobody knows the zip when the costing is done...

However you have inadvertently added another possibility which is adding location specific data and including a zip facility is a good idea provided it isn't a specific find/calc requirement.

So you're saying two tables?

City

Route

(plus at least 3 more - Itinerary, Costing & Ferries)

Are you proposing that every city populates a flat list as opposed to my route list (city to city)? Where does the mileage go? Another look-up perhaps???

Any dept/dest pop-up list is going to end up being huge...

I look forward to hearing how your idea progresses. Thanks for your interest.

Tim

[moonunit]

Sorry Sanjai,

My response was from the subscribed email I received. For some weird reason not all your posted message was included in the email...

So my comments ignore your last 2 paragraphs...

(I'm considering them now...)

Tim

[Sanjai]

Hi Tim,

Let me have a look at your database when I go home. If you have some more files which you think would be helpful, please upload them so that I can get the details. Please let me know if you are designing something which is in the development mode or you are working on something as an enhancement. Because if it is not a new development then we should think of how to introduce the required changes instead of designing.

I would answer your questions one by one:

Any dept/dest pop-up list is going to end up being huge...

The value list would be created based on the City name field in City table. We only would have to take care of updating city table. You can use the same process in the Ferry table to enter the departure and desitnation station and relate it with the Route table using the calculation field cRoute from my previous post.

(plus at least 3 more - Itinerary, Costing & Ferries)

I understand to keep a Ferries table to keep the Ferry information.

How about we keep the Itinerary and Costing information in one table called Billing.

Just an idea we can discuss more on it and we can come to a conclusion.

However you have inadvertently added another possibility which is adding location specific data and including a zip facility is a good idea provided it isn't a specific find/calc requirement.

It allows us to keep duplicate city names.

Are you proposing that every city populates a flat list as opposed to my route list (city to city)?

Yes, if you like :. The reason is to have a simple model.

Where does the mileage go? Another look-up perhaps???

I couldn't see the description of all the fields in the fields which you have listed in the mileage table in your post. Knowing how to calculate the mileage would be helpful.

Edited November 10, 2005 by Guest

[moonunit]

Hey Sanjai,

In answer...

This IS a development mode project, but it's based on simplifying and improving on the current multiple XL spreadsheet system in use.

However this a non-computer-literate business. Making data input more detailed may not be viewed as a bonus.

As you'll see when you get home, my initial attempt is based on compiling a 'from-to & miles' record entry list. Yes this involves massive duplication of one of the cities in each equation, but if the route you want isn't there, in the portal window, you create a new record - actually creating a duplicate would be hard (though, as far as I've got, actually creating a new record at all, let alone a dupe, is pretty hard!). Locking the mileage in (with a user account name & timestamp) is a good way of ensuring they retain faith in their own data and saves them from the manual task of recalculating the mileage each time.

[Putting that into perspective, every mileage is currently calculated by hand - route-planning software is deliberately not used...Assuming this database works, providing a central registry of previously used routes and mileages should reduce their task by hopefully 50% in time, and maybe 70% in five years time!]

Sorry I don't have any other files worth looking at though I'm currently working on the Itinerary layout. I guess it is just possible that in due course the costing side of this might need it's own table...In the short term this is a quote project. Once the quote is accepted this needs to move into job mode.

There is also one other issue to consider.

Assuming that somehow a mileage is locked in (I'm mentally picturing one of those road atlas mileage pages - all the cities are repeated across the top and down the side of a table, with the mileage set on the intersection between departure and destination perhaps?) the mileage table needs to be available for multiple quotes.

If this were to become a stand-alone one-off quote/job document all mileages and cities would have to be located in a remote db...Just a thought!

I will check back later - it's a couple of hours off my bed-time.

Cheers

Tim

[Sanjai]

Tim,

I apologize that I didn't get a chance last night. I would go through it and probably if you are online tomorrow on Saturday, we can work on it together. The posting is getting longer so do you think we can use our personal IDs to solve the problem? If yes send me a private message giving your email id and I would be happy to reply you from my personal mail account.

[moonunit]

Sanjai?

What happened?

I (think) I left you a private topic, but haven't heard back from you...

Tim

[Sanjai]

Hey Tim,

I apologize. I was waiting to hear from you and after reading your message I looked into my bulk folder and it was there. Let us work on it starting from today evening and try to get it resolved. I don't know why rediff automatically transfers new emails to the bulk folder instead of Inbox. Sorry for the delay.

[moonunit]

Nice to hear you're still alive Sanjai...

It's my bed time now...The db has progressed since we last posted. I'll tidy it up and re-post it tomorrow, so you can have a look.

Cheers

Tim

[moonunit]

Hey Sanjai,

As I mentioned, things have progressed...I took your initial idea of a departure/destination list a little further...

A new entry in the Departure City table is copied into a destination table...

I've added a Route table, which comprises selection of a city from each list; add mileage and commit. A script then inverts the departure city so the route works in both directions.

I've added in countries.

At the moment all the data-entry elements are in one layout, but all these will probably spin out into their own windows.

I've added an 'Itinerary' table which allows routes to be assigned to dates.

Other tables to be added...

Ferries

Driver hours / trip time (possibly)

Double driver

Border crossing info (tbc)

Costings

So far I've had to script control new cities & new route entry. This works but it would be nicer to have a cleaner data-entry.

The city and route finding is very dependent on consistent spelling - it would be really good if, somehow, a selection of close spellings were displayable after the match lookup has taken place...

Yes, this could be accomplished by having a pop-up or pull down, but as I expect the db to have thousands of records - London alone could be 1000 - any pop up would need to be filtered...

There is also the more final issue of how some elements of this db are used...Mileages evidently need to be available to every user; but the current design seems to suggest that each costing would have to be a separate file.

I'd appreciate your giving it the once over and suggesting how I might improve it, but there's no rush; I've got other stuff going on this week...

Tim

[moonunit]

Sanjai,

Have you had a chance to take a look yet?

Tim