Looking ahead (1 Viewer)

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
As some of you already know, I work in public transport, and I am thinking ahead about creating a database that will allow us to keep tabs on disruptions that occur on our routes; some disruptions will affect one route, some will affect multiple, and a few will affect all. We have 120+ route numbers (alphanumeric) to consider, and these can change over time.

What I would like to do is have the ability to record disruptions with the following info

Start Date
End Date
Route(s) affected
What the disruption was
What the effect was
What type (eg Gas, Water, Electricity, Re-surfacing, Landscaping) - probably best stored in a table so they can be added to
Postal Code area (first half)

It would need to be as easy as possible for a user to select as many routes are affected (preferably if they could type them in separated by commas would be nice), to then be able to be recalled at a later date either by postal area search, a disruption type search or by searching for one route number, all within selected date parameters.

Clearly I will need a table for the routes, a table for the postal areas, a table for the disruption types, and a table for the disruption details - all fairly straight forward so far. What I can't work out is how to handle the route numbers to make them searchable later on, if they are stored in multiple? The same applies to the postal code search - what if more than one postal area is affected by a disruption.

As I say, I am at thinking stage only at the moment, so now is the time to gather thoughts and opinions to get the structure right from the start, rather that making a bit of a mess of it and having to faff around with vba just to get it sort of working.

What do you think ladies and gents?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:07
Joined
May 7, 2009
Messages
19,169
suggest, putting each route as checkbox, so it's just a tick away
 

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
I used tick boxes on another database in this way - bound to a field in the underlying table which made querying really easy - adding a route, however, meant having to change forms and reports to match, which was laborious; I suppose this time, there will only be a couple of forms and a couple of reports, but it means that I will have to make any changes and users can't just add a route to the list.

Are tick boxes the only way to make it searchable?
 

plog

Banishment Pending
Local time
Today, 18:07
Joined
May 11, 2011
Messages
11,612
It would need to be as easy as possible for a user to select as many routes are affected (preferably if they could type them in separated by commas would be nice),

You just described a many to many relationship, those require a new table, specifically a junction table (aka associative entity: https://en.wikipedia.org/wiki/Associative_entity). A route can have many disruptions and a disruption can affect many routes--that requires a table to sort out which route goes to which disruption.

I know you have ideas about how you want the data input, but at this point you need to put that aside and focus entirely on your table structure. My advice is to create a spreadsheet with a bunch of fake data that a disruption could produce. Then normalize (https://en.wikipedia.org/wiki/Database_normalization) that data set--construct the table and fields required to hold the data. Do this in Access, set up the Relationship tool, then post back here a screenshot of it so we can help you get the structure correct.
 

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
Fantastic advice, thank you :)

It won't be today, but I will do this and post back. Cheers :)
 

isladogs

MVP / VIP
Local time
Today, 23:07
Joined
Jan 14, 2017
Messages
18,186
Agree with plog ...
As you will have a large number of routes, I can't see how arnel's checkbox idea would work.

As for postcodes, I'm not sure whether you're UK based.
If you're not then stop reading now...

If you are in the UK, postcode area refers to the initial letter(s) e.g. BS, EC, L etc
The outward part e.g. BS7, EC1 is called the postcode district
In case you're interested, here's a screenshot explaining the terminology from an app I'm just completing



NOTE area, district & sector are official terminology
I added zone to further break down the postcodes
 

Attachments

  • Capture.PNG
    Capture.PNG
    22.6 KB · Views: 204

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:07
Joined
May 7, 2009
Messages
19,169
im talking about form design.
 

isladogs

MVP / VIP
Local time
Today, 23:07
Joined
Jan 14, 2017
Messages
18,186
Hi arnel

im talking about form design.

Yes I thought you were but can't see how it would be practical if you have a large number of routes.
Surely combo box or listbox would be better?
 
Last edited:

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
I can see how the tickbox would work, having used it before - does make it very easy to query the data - however, with up to 150 options, I worry that it may swamp a user?

Your app looks good @ridders - way more than I need though. Ours would simply be the first half (TW1, TW2, W4 etc); very occasionally, we might get one that straddles a couple of postal codes, but that would be rare.

If we could see everything that has happened in W4 between two dates for example, that would be dandy.
 

isladogs

MVP / VIP
Local time
Today, 23:07
Joined
Jan 14, 2017
Messages
18,186
Ahhh ... my memory was correct. London postcodes.
If its TfL routes, surely there are more than 150 bus routes in all.

Anyway, I know you don't need my app, just sharing my 'knowledge'.
However, if interested there are free versions in sample databases - both postcodes & postal addresses
 

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
It is some TfL routes, some non-TfL routes; like all public transport nowadays, the contracts to run TfL routes are awarded to private companies, hence the need to be able to vary the route numbers as contracts change. The same applies outside the London area, where we also operate. Indeed, if I can, I would like to be able to gather information by either route number or postcode (anywhere in the UK, at the moment - I will cross the bridge of international codes if that ever arises!)
 

isladogs

MVP / VIP
Local time
Today, 23:07
Joined
Jan 14, 2017
Messages
18,186
Perhaps the postcode or postal addresses databases would be useful after all?
 

Solo712

Registered User.
Local time
Today, 19:07
Joined
Oct 19, 2012
Messages
828
I can see how the tickbox would work, having used it before - does make it very easy to query the data - however, with up to 150 options, I worry that it may swamp a user?

Your app looks good @ridders - way more than I need though. Ours would simply be the first half (TW1, TW2, W4 etc); very occasionally, we might get one that straddles a couple of postal codes, but that would be rare.

If we could see everything that has happened in W4 between two dates for example, that would be dandy.

One thing I am not clear about is whether disruptions relate to routes, or (really) whether they relate to areas (described by postal codes), which the routes pass through. Something tells me that it is the latter, i.e. that the disruptions would be assigned to one or more areas, which in turn would distribute over bus routes assigned to them. Surely if there is road work in an area that affects three bus routes, it is better to assign it to the area rather than entering it three times for the buses.

So what I would suggest is that the Disruption table that you described above, i.e.
Code:
Start Date
End Date
[COLOR="SeaGreen"]Route(s) affected[/COLOR] ' [COLOR="Magenta"]Postal Code area ?[/COLOR]  
What the disruption was
What the effect was
What type (eg Gas, Water, Electricity, Re-surfacing, Landscaping) - probably best stored in a table so they can be added to
Postal Code area (first half)

Doing it this way, i.e. calculating the affected bus routes rather than assigning the disruptions to them, will easily save you a lot of work. You can find which bus routes are affected by the disruptions by mapping the areas to bus routes. Seems like better normalization to me. Just my $.02

Best,
Jiri
 

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
I like your thinking - if I am getting you right, have the postal code areas that each route passes through noted against the route number, and then when there is disruption in that area it will lookup what the affected routes are?

The only slight issue for me would be when a route is affected by something in an adjacent area - for example, we can have an event in TW9 that will ricochet across TW1, TW2, TW13 & TW15; how would I assign information for a route in TW15 when it goes nowhere near TW9?

Please don't take this post as me being argumentative, as I am genuinely not - I am aware that I have in the past taken the 'bull at a gate' approach, and my normalisation is not necessarily as good as it could or should be; I am hoping to learn more about it as part of this exercise, hence the questions.
 

isladogs

MVP / VIP
Local time
Today, 23:07
Joined
Jan 14, 2017
Messages
18,186
I also like Jiri's idea here....

To partly answer your question, the first thing you need to know is the adjacent postcodes so you know which may be affected. You can get that info from the website www.doogal.co.uk and store that info in another table
 

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
I worded that badly when I said adjacent, as the vagaries of London means that the propagation of disruption can be really strange; I'm just about to have dinner - back soon with a better example.
 

Solo712

Registered User.
Local time
Today, 19:07
Joined
Oct 19, 2012
Messages
828
I like your thinking - if I am getting you right, have the postal code areas that each route passes through noted against the route number, and then when there is disruption in that area it will lookup what the affected routes are?

The only slight issue for me would be when a route is affected by something in an adjacent area - for example, we can have an event in TW9 that will ricochet across TW1, TW2, TW13 & TW15; how would I assign information for a route in TW15 when it goes nowhere near TW9?

Please don't take this post as me being argumentative, as I am genuinely not - I am aware that I have in the past taken the 'bull at a gate' approach, and my normalisation is not necessarily as good as it could or should be; I am hoping to learn more about it as part of this exercise, hence the questions.

Good point! So if an event can relate to x number of areas then we have one-to-many relationship ans it should be taken out of the table of disruptions and asssigned to another table, say, DisruptionAreas. Then a single event could potentially affect a number of postal codes and naturally it would also, indirectly, increase the number of bus routes affected.

Best,
Jiri
 

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
OK, dinner munched.

Hopefully the attached map will give some idea of what I am prattling on about :D

If something happened at the point where the red blob is, it could radiate back throughout the area within the red lines. The blue line represents, very roughly, the path of some routes - as you can see, these are nowhere near the red blob and are not even in an adjacent area, but I would still need to record the disruption against the route number affected. (The idea being that this can help us build up a picture of what sort of things affect a particular route to allow us to try and mitigate disruption better for future disruptions).

Now, it is not always the same - - if that same blob was moved SW to the TW2 area, I would expect the disruption to spill into TW1, KT1 and potentially TW4.

I the disruption arose in TW4 however, it would be quite doubtful if it would be felt at all in TW1 or TW2.

However, I still need to be able to record the information where the disruption originated from (could potentially have two though - area disruption felt, area of source?)
 

Attachments

  • Map.jpg
    Map.jpg
    100.6 KB · Views: 66

isladogs

MVP / VIP
Local time
Today, 23:07
Joined
Jan 14, 2017
Messages
18,186
And I hoped you enjoyed it ...
I've been watching Masterchef & now Rick Stein in Mexico.

Your image confirmed what I thought you meant before.
I know you can get adjacent postcodes from Doogal
https://www.doogal.co.uk/FindPostcode.php
As an experiment I typed in a random address in TW1 and asked for postcodes within 500m. The results are available as a csv file & on a map
Attached is csv file converted to xls so I could upload here

Now I realise this is probably more detail than you require
I'm just about to reply to an email from the site owner, Chris Bell, so I'll refer him to this post & ask if it can be done at district level e.g. TW1, TW2 etc.

Chris is incredibly helpful and will tell us if it can be done easily
Will let you know what he says
 

Attachments

  • postcodes.xls
    118.5 KB · Views: 64
Last edited:

fat controller

Slightly round the bend..
Local time
Today, 23:07
Joined
Apr 14, 2011
Messages
758
Thanks :)

It is certainly interesting - I am trying to picture in my head how I would relate that in reality.

I will knock up a spreadsheet at some point over the coming days as suggested, and then I will post it back here before going any further - be interesting to see the advice on normalising the data - I have got lots to learn :)
 

Users who are viewing this thread

Top Bottom