Correct number of tables, and relationships

[fat controller]

I am starting a fresh database, and in comparison to the last time I commenced a project like this, I am a little bit less 'green' - so, rather than work around a structure that is OK but not ideal, I would like to try and get the structure right from the start, and to do so I could do with some guidance please.

As it stands, for this first stage, I think I need either three or four tables, as follows:

tblRouteRecords
ID - Autonumber
Route - Text
DestUp - Text
DestDown - Text
StreetsUp - Text
StreetsDown - Text
IssueReason - Text
Status - Text
DateFinalised - Short Date
DateValidFrom - ShortDate
TemporaryIssue - Yes/No
AuthVehicles - Text
Warnings - Text

tblDeadRuns
ID - Autonumber
Route - Text
Description - Text
Detail - Memo

tblStands
ID - Autonumber
Route - Text
StandName
Directions
Availability
OpRestrictions
MealRelief
FerryVeh
Display

Now, I am wondering if I should have one more table for best normalisation, which would be:

tblRoutes
ID - Autonumber
Route - Text
DestUp - Text*
DestDown - Text*
StreetsUp - Text*
StreetsDown - Text*

*These fields would then not appear in tblRouteRecords

At the output end, I am needing to produce a report using the data from all tables. The data from tblRouteRecords will be one record, the data from tblDeadRuns will be numerous records, the data from tblStands will be numerous records, and the data from tblRoutes will be one record.

In short, a report that shows all available stands and all available dead runs for each route. If possible with the report, I would like the report to automatically re-size depending on the number of stands and dead runs that are included (as in, not showing a load of blank space if there is only three of each on a particular route).

Am I on the right track, and what sort of relationships would I need to create between those tables?

EDIT - also, the report that is produced and finalised, I would need to stay the same as it was at the time it was issued (and be able to be reprinted from the database), and not change if there are changes made to fields such as StreetsUp, StreetsDown etc, so I am assuming that I will need to have a valid from and to date in each of the tables with the exception of tblRouteRecords?

 

[bob fitz]

Can you tell us a little of what the db is for

 

[fat controller]

I will try, although it may not make a lot of sense to someone outside my industry.

We operate bus and coach services via numerous different routings; legally, we have to issue a document which shows all of the information recorded above. The information can, and is modified from time to time which then requires a replacement document to be issued - but, we must also retain the information from the old document in its entirety and be able to recreate that document at any time. As a general rule of thumb, there should only ever be one active document at any one time - however, there is occasionally the need for a temporary document to run concurrently to allow us to cope with route alterations when there are major roadworks etc. There is also the need for the ability to create a new document in advance of it going live and superseding the prior one.

I initially took a look at the end document (which also has a fixed format that we must adhere to), and to have a single table that would hold the relevant information for each document as one record for each document, would have meant somewhere in the order of around 160 fields, which would allow for a maximum of 15 stand points, and only one huge memo field to show all the dead runs - having read and at least tried to learn from the wealth of information here over the years , I realised that this was a warning flag that the data was not well normalised, and that it was time to seek further advice.

As things progress, there will be other tables required to store further information, however they are likely to only be linked to the table that I have called tblRoutes above.

Hope this makes sense

 

[arnelgp]

system analysis is the hardest part.
its okey to have as many fields in your table.
sooner or later you will find that you actually need this fields or not really wanted as your database go live.

ms access itself is not built in a day. it has gone revisions and patches over time.

 

[fat controller]

Thanks

I have a funny feeling that I am going to have to have some duplication of data, purely to store the historical stuff; bit of a pain in the rear, but sadly quite endemic in my industry.

 

[AccessBlaster]

You might consider changing the simple "ID - Autonumber" to something a little more useful see below. At some point you may need to use that primary key in another table as a foreign key.

In short having two fields named ID in the same table could be confusing to trouble shooters.

[B][U]tblRouteRecords[/U][/B]

 [COLOR="Red"][B]RouteRecordsID [/B]-  [COLOR="Red"][B]PK Autonumber[/B][/COLOR][/COLOR]
 Route - Text
 DestUp - Text
 DestDown - Text
 StreetsUp - Text
 StreetsDown - Text
 IssueReason - Text
 Status - Text
 DateFinalised - Short Date
 DateValidFrom - ShortDate
 TemporaryIssue - Yes/No
 AuthVehicles - Text
 Warnings - Text

 

[Gasman]

Thanks

I have a funny feeling that I am going to have to have some duplication of data, purely to store the historical stuff; bit of a pain in the rear, but sadly quite endemic in my industry.

I queried this recently with a project I was (still am really) doing, and was told that is was OK in some circumstances, and it sounds like yours is one of them.

Here is the thread, post #6
http://www.access-programmers.co.uk/forums/showthread.php?t=288296&highlight=xlTop

 

[gemma-the-husky]

why do you want route up/down and streets up/down. what do they do?

I assume you have a "stops" table. is that "stands"?

I would have thought you need tables


Routes (routeid[autonumber], designation, timestart etc)
Stops (stop id[autonumber], location)
routeDetails(routeid, stopid, stoporder, requeststop_y/n, scheduled_arrivaltime)

so the routedetails consists of a series of stops. That way you certainly don't need 160 fields per route. You need a handful of fields per route, and a handful of fields per stop on the route, and you aren't limited to a maximum number of stops.

it might be useful to have a "standard route" table, so you can repeat the same routedetails starting at different times. every hour, on the hour, sort of thing.

you certainly want to spend some time planning this carefully.


you may need a drivers table, a bus table and so on, especially if the buses have different capacities, different facilities for handling disabled passengers, and so on.

 

[fat controller]

There are a number of legal constraints and obligations that we have to comply with, many of them are quite archaic in their methods, but they are here to stay I'm afraid - one of those legal obligations is that we must have declared documentation that shows exactly which streets/roads are traversed in either direction. In many cases, these are exactly the same in both directions, with one direction being the complete reverse of the other - however we must still declare and document each direction separately, and have the ability to have those directions differ.

Stops are not required in this instance - stands are quite different, in that they are designated points where a vehicle can be terminated, wait and/or have its direction changed. There are 9 different variables that we must declare for each stand, and just to make matters all the more interesting, the same stand can be used by a number of different routes, however the identification name for that stand can be different for each of those routes.

Sadly, the infrastructure and the designation of the identification names/numbers are not ours to change, we are merely contractors that are duty bound to use them.

The 160 fields comprise of 15 x the 9 fields required for each stand, hence allowing up to 15 different stands on each route. Some have only three or four, some have more. The remainder are single fields that relate to other information that is unique.

Ideally, if a route only has four stands, on the end report document I would like it to show only the four, and not then have 99 wasted fields that would need to be hidden somehow. I will of course ask for advice in the reports section when I get that far, as I also would like to achieve a certain layout in terms of pagination.

EDIT - time is irrelevant by the way; this is not for scheduling purposes, we have very specialised software that does that. The document that this produces can live in its current form for a number of years, but can also be superseded at any time, even on a temporary basis, and I will be setting a review date for each of the documents too (most likely annually, but at this stage I am not going to worry about that)

 

[Tieval]

For something so heavily regulated it seems incredible that you can wait for hours and then have two turn up

 

[gemma-the-husky]

well it's still the same thing.

for each route, you need to store a route-details record of the streets traversed. not sure how you designate left/right north/south etc.

The 160 fields comprise of 15 x the 9 fields required for each stand, hence allowing up to 15 different stands on each route. Some have only three or four, some have more. The remainder are single fields that relate to other information that is unique.

ie not 160 fields.

just break it down into subtables of streets and stands.
I presume a stand is located on a street. It doesn't matter, really. However it is structured, that's the analysis you want.

so not

route1, stand1, stand2, stand3, stand4 ....

but

route1
stand12, designation XXXX, ordervisited 1
stand36, designation AAAA, ordervisited 2
stand17, designation BBBB, ordervisited 3
stand25, designation CCCC, ordervisited 4

etc.

maybe route details includes both streets and stands, so you need 2 subtables.
maybe the street detail can include (a pointer to) the stand information, so you need 1 subtable, and the subtable has a subtable.


so maybe you can use this sort of thing.
route1
street, order 1, high st, from north, stand 0
street, order 2, brook st, from east, stand12
street, order 3, bright road, from north, stand 0
street, order 4, hull avenue, from west, stand 36
etc.

 

[fat controller]

For something so heavily regulated it seems incredible that you can wait for hours and then have two turn up

Sadly, all the heavy regulation only relates to the legal and structured side of things; we cannot legislate for the hundreds of variables such as accidents, emergencies, roadworks, delivery lorries, stupidly parked vehicles.....) - this is made all the harder when the buses are scheduled anything from 2 mins to 20 mins apart. There is a hell of a lot more involved in running a bus service than you might first imagine.

 

[fat controller]

well it's still the same thing.

for each route, you need to store a route-details record of the streets traversed. not sure how you designate left/right north/south etc.



ie not 160 fields.

just break it down into subtables of streets and stands.
I presume a stand is located on a street. It doesn't matter, really. However it is structured, that's the analysis you want.

so not

route1, stand1, stand2, stand3, stand4 ....

but

route1
stand12, designation XXXX, ordervisited 1
stand36, designation AAAA, ordervisited 2
stand17, designation BBBB, ordervisited 3
stand25, designation CCCC, ordervisited 4

etc.

maybe route details includes both streets and stands, so you need 2 subtables.
maybe the street detail can include (a pointer to) the stand information, so you need 1 subtable, and the subtable has a subtable.


so maybe you can use this sort of thing.
route1
street, order 1, high st, from north, stand 0
street, order 2, brook st, from east, stand12
street, order 3, bright road, from north, stand 0
street, order 4, hull avenue, from west, stand 36
etc.

Not all are on streets, no, and there is a standard set of information. Wen I get home later, I will post up some of the information that will give you an idea what the final document needs to look like. I think that the way you are putting it though, would be ideal and possibly covered by the four table structures above? My only question there would be how historical data was stored when the routing or standing arrangments change?

 

[fat controller]

OK, I would have posted a sample had I remembered to pick up my USB stick from my desk.... doh! I will try and knock something up in Word now, and post back shortly

 

[gemma-the-husky]

"street" was hypothetical

the idea is to consider how the route name, route details (road sequence), stands, stops etc are all related, and to produce a structure that lets you model the routes accordingly, in a normalized fashion.

so not a single table with up to 15 stands, but definitely a table for the route(s), and another table for all the stands linked to those routes.

I have developed systems to manage logistics routing - delivery routes - which is similar, but your bus routes design obviously has additional nuances.

 

[fat controller]

OK - it is a mock up, but pretty much the layout that the final report must have. The section for 'Stands' is repeated again and again for the various stands that may be available along the route - for long routes, there can be a good few of these.

 

[Uncle Gizmo]

The Irish have a turn of phrase which is quite amusing and is also quite Insightful. When asked for directions they may say "Well if I was going there I wouldn't start from here" ... It's one of the problems you face when you already have something, you start thinking about it in terms of what you have and not the underlying structure. So for instance you are seeing a big table with lots of Fields however if someone sat down with you and said I want you to build a database that does this/and /or that, then you might well think about it in a different way. Really you've got to think about it from the point of view of the tables. Now I know that's difficult if you already have all this data, I mean if you start creating new tables with a different structure than your existing data, how are you going to get that data into the new tables? This might well be such a big problem that you are forced to go with poorly designed tables. But I still think it would be worth attempting to start off designing it as if you didn't know what it already look like, what tables would you need? how do they fit together? You might be surprised, you might start to see that it's simplifies your existing setup. You might even see an easy ways to reuse the information you already have.